If any record for the project is marked Finished, highlight all records for that product in green. Bill and Mike try different methods, such as COUNTIFS, LOOKUP, and Conditional Formatting.
Transcript of the video:
Bill Jelen: Hey, welcome back, it’s time for another Dueling Excel Podcast.
I’m Bill Jelen from MrExcel.
I’ll be joined by Mike Girvin from Excel Is Fun.
This is our Episode 157: If Project is Finished, Mark All Records in Green.
Hey, welcome back, it’s time for another Dueling Excel Podcast.
We have a data set-up here in the questions down here from YouTube.
In the first column, there is IDs of 5 different projects.
And there’s duplicates and the Status has changed from Start to Finish, Start to Correction and so on.
And if anyone of those records is marked as Finish, we want to change the color to all of the records to be green.
All right, and so I’m going to do this using conditional formatting and like how Mike suggests, it’s best to build a conditional formatting formula off to the side to make sure that it’s working first.
So, I’m going to use the new =COUNTIFS($A$2:$A$11,$A$2,$C$2:$C$11,”Finish”), all right.
And what this is going to do for us is count how many times this ID code appears with the word “Finish.” And it’s probably going to be either 0 or 1.
It’s unlikely that something would be in there would a Finish of twice, although I guess it would be possible for that to happen.
So, once we have the =COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11,”Finish”) working, we’re going to copy everything from the EDIT Mode here or Ctrl+C, we’ll go back and select all of our cells Alt+OD, we’re going to create a new Rule and Use a formula and just paste that rule =COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11,”Finish”) in there.
It’s really important that rule is written from the point of view of cell A to the top left corner cell and then in this case, it is so a nice little format there, Fill, I’ll choose this green, click OK, click OK, click OK and any of the projects that are finished are now marked as Finish.
So, this one, we changed the Finish, you see that all of those get marked as Finish.
So, there you go, Conditional Formatting.
Mike, let’s see what you have.
Mike Girvin: Thanks, MrExcel.
Oh, you get the point.
=COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11,”Finish”), that is a beautiful solution and what’s so cool is that it doesn’t matter how this data set is sorted, it will always get the right conditional formatting.
Now, I’ve tried this a bunch of different things and I did not think to use =COUNTIFS.
All right, so if this column is sorted, we can use a different formula but if we unsort it, I’ll show you that it doesn’t work.
Hey, and I’m noticing that this sorted, right?
And the word Finish is always going to be the last entry for whatever particular code this is.
So, I’m going to use the LOOKUP function now.
Now, =LOOKUP, this isn’t H or V =LOOKUP, this is just straight =LOOKUP($A2,$A$2:$A$11.
The =LOOKUP vector, if I highlight this right here and hit F4 that means =LOOKUP will find this particular item, that position of the last one because it’s sorted, we’re doing a proximate match, it will always find the position of the last one.
That’s what that =LOOKUP vector does.
It’s kind of like a match, right?
But result vector, F4, that’s the range that has the value we want to go and get.
So, right now, the =LOOKUP vector will find position 1, 2 because that ID is the last one.
The result vector will give me the Finish =LOOKUP($A2,$A$2:$A$11).
Let’s go ahead and enter this, Ctrl+Enter, Copy it down and over and look at that, it’s given me Finish everywhere.
Now, I’ve highlighted this.
I’m going to hit the F2 key for the active cell and now, I’m going to edit this =LOOKUP($A2,$A$2,$A$11,$C$2:$C$11)=”Finish.” Remember, this formula will only work if it’s sorted.
Ctrl+Enter to populate all the way through.
So you can see we get our TRUEs.
Here’s the =COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11,”Finish”).
And by the way, the 1 or the TRUE and FALSE will all be interpreted correctly by the conditional formatting dialogue box.
So now, I’ve highlighted that top corner cell, copied it, highlighted, Alt+OD, Alt+N, arrow, arrow, arrow, tab, Ctrl+V and then apply some formatting like that green or something.
Click OK.
And if the other ones need to be orange, I’ll just highlight everything and the ones that are not conditionally formatted will have the orange.
The only problem with this formula and the benefit of this formula is if this is sorted.
On day 8, well, this is working fine but not this formula.
Ctrl+Z.
All right, throw it back to MrExcel.
Bill Jelen: All right, Mike, anytime you pull off that old =LOOKUP function, I know we’re in for a ride.
I want to thank everyone for stopping by.
We’ll see you next time for another Dueling Excel Podcast from MrExcel and Excel is Fun.
I’m Bill Jelen from MrExcel.
I’ll be joined by Mike Girvin from Excel Is Fun.
This is our Episode 157: If Project is Finished, Mark All Records in Green.
Hey, welcome back, it’s time for another Dueling Excel Podcast.
We have a data set-up here in the questions down here from YouTube.
In the first column, there is IDs of 5 different projects.
And there’s duplicates and the Status has changed from Start to Finish, Start to Correction and so on.
And if anyone of those records is marked as Finish, we want to change the color to all of the records to be green.
All right, and so I’m going to do this using conditional formatting and like how Mike suggests, it’s best to build a conditional formatting formula off to the side to make sure that it’s working first.
So, I’m going to use the new =COUNTIFS($A$2:$A$11,$A$2,$C$2:$C$11,”Finish”), all right.
And what this is going to do for us is count how many times this ID code appears with the word “Finish.” And it’s probably going to be either 0 or 1.
It’s unlikely that something would be in there would a Finish of twice, although I guess it would be possible for that to happen.
So, once we have the =COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11,”Finish”) working, we’re going to copy everything from the EDIT Mode here or Ctrl+C, we’ll go back and select all of our cells Alt+OD, we’re going to create a new Rule and Use a formula and just paste that rule =COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11,”Finish”) in there.
It’s really important that rule is written from the point of view of cell A to the top left corner cell and then in this case, it is so a nice little format there, Fill, I’ll choose this green, click OK, click OK, click OK and any of the projects that are finished are now marked as Finish.
So, this one, we changed the Finish, you see that all of those get marked as Finish.
So, there you go, Conditional Formatting.
Mike, let’s see what you have.
Mike Girvin: Thanks, MrExcel.
Oh, you get the point.
=COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11,”Finish”), that is a beautiful solution and what’s so cool is that it doesn’t matter how this data set is sorted, it will always get the right conditional formatting.
Now, I’ve tried this a bunch of different things and I did not think to use =COUNTIFS.
All right, so if this column is sorted, we can use a different formula but if we unsort it, I’ll show you that it doesn’t work.
Hey, and I’m noticing that this sorted, right?
And the word Finish is always going to be the last entry for whatever particular code this is.
So, I’m going to use the LOOKUP function now.
Now, =LOOKUP, this isn’t H or V =LOOKUP, this is just straight =LOOKUP($A2,$A$2:$A$11.
The =LOOKUP vector, if I highlight this right here and hit F4 that means =LOOKUP will find this particular item, that position of the last one because it’s sorted, we’re doing a proximate match, it will always find the position of the last one.
That’s what that =LOOKUP vector does.
It’s kind of like a match, right?
But result vector, F4, that’s the range that has the value we want to go and get.
So, right now, the =LOOKUP vector will find position 1, 2 because that ID is the last one.
The result vector will give me the Finish =LOOKUP($A2,$A$2:$A$11).
Let’s go ahead and enter this, Ctrl+Enter, Copy it down and over and look at that, it’s given me Finish everywhere.
Now, I’ve highlighted this.
I’m going to hit the F2 key for the active cell and now, I’m going to edit this =LOOKUP($A2,$A$2,$A$11,$C$2:$C$11)=”Finish.” Remember, this formula will only work if it’s sorted.
Ctrl+Enter to populate all the way through.
So you can see we get our TRUEs.
Here’s the =COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11,”Finish”).
And by the way, the 1 or the TRUE and FALSE will all be interpreted correctly by the conditional formatting dialogue box.
So now, I’ve highlighted that top corner cell, copied it, highlighted, Alt+OD, Alt+N, arrow, arrow, arrow, tab, Ctrl+V and then apply some formatting like that green or something.
Click OK.
And if the other ones need to be orange, I’ll just highlight everything and the ones that are not conditionally formatted will have the orange.
The only problem with this formula and the benefit of this formula is if this is sorted.
On day 8, well, this is working fine but not this formula.
Ctrl+Z.
All right, throw it back to MrExcel.
Bill Jelen: All right, Mike, anytime you pull off that old =LOOKUP function, I know we’re in for a ride.
I want to thank everyone for stopping by.
We’ll see you next time for another Dueling Excel Podcast from MrExcel and Excel is Fun.
