Sowmiya needs to highlight entire row with highest value with different data sets.. for eg coulmn A having id numbers , id 1 and 2 etc.. column b having lead count.. coloumn C having dates. Id1 taken lead count 6, 9 , 10 in different dates.. id number 2 having lead count 15 , 10 , 9 in different dates. I need to highlight unique highest values for each id. Output I need is to highlight the row id1 against lead count 10 and id 2 against 15 which is the highest value
Transcript of the video:
Learn Excel from MrExcel podcast episode 2352.
Highlight the top row for each ID. Hey welcome back to the MrrExcel netcast.
I am Bill Jelen. A question today from Sowmiya on YouTube.
She has several different ids.
ID1 has a lead count of 6, 9, and 10 on different dates.
ID2 has lead count of fifteen ten and nine in different dates.
She wants to highlight the the entire row with the top lead count for each ID.
So this is the formula that i sent. Sowmiya says "No I have 250 of these".
I get it.
When you are creating conditional formatting, the way to do it is don't try and build it in the conditional formatting dialog box.
Build it outside the Conditional Formatting Dialog Box. So the formula here.
Let's make it larger.
I have data today in rows 2 through 23. If you have 250 IDs then your data is larger.
Adjust the formula references to match the size of your data. So the MAXIFS.
We are going to look through all of the lead counts in B2 to B23.
This is what we're taking the Max of. The criteria is look through A2 to A23.
Notice that there's 4 dollar signs in $B$2:$B$23.
Then there are 4 dollar signs in $A$2:$A$23. Then the most important part is the third argument.
When you are creating conditional formatting you have to be writing a formula as if it applies to just the cell that you are in. In the third argument, you want to say always look in column $A.
But the 2 has no dollar sign! That means that it's allowed to move to other rows.
So imagine as this gets copied down.
It's going to be pointing at different rows. The same thing for $B2 with a single dollar sign.
We will say from among all of the things with the Lead ID of 1. If this item is the Max.
So if $B2, the item in this row is the max.
And here we will just take a look at this formula as we copy it down.
So we come down here. See in the formula the $B$2 to $B$23 stays.
The $A$2 to $A$23 stays. But the $A$2 is changed to $A11 because there's no dollar sign before the row.
And the $B2 changes to $B11. Once you have that formula working in the cells.
Then you are going to put the cell in edit mode by pressing F2. Select all of the characters in the cell.
Do not copy the cell.
You have to copy it either from the Formula Bar or in edit mode. Ctrl+C to copy.
Clear the rules. Select all of your data.
Make sure that A2 is the Active Cell. Select Conditional Formatting then New Rule.
Use a Formula to Determine Which Cells to Format.
Then Ctrl+V to paste. Be very careful in this box. If there is a mistake.
If you press the left arrow key.
Excel is going to start adding cell references. This happens because down here we are in Enter mode.
If you press F2 then you are in Edit mode. You can move left and right in the box without a problem.
Then click Format. Choose whatever format you want.
I will choose a nice light color like that. Click OK. Click OK. And now for ID1.
21 is the largest. And the whole row gets highlighted.
These don't have to be in any particular sequence.
It can be in date order and it's still going to work. Let's change 1.
Take this cell for ID1 and change it to 99 so it's the largest. See, it changes which row is highlighted.
You don't need the formulas in column D.
You just used those as a helper to help you build the formula. And make sure it's working before it goes in. Alright Sowmiya.
Hopefully that helps.
Please for everyone and Sowmiya subscribe and ring that bell.
I am trying to get a better Merchandise experience down below.
My new book MrExcel 2020 Seeing Excel Clearly.
Click the "i" in the top right hand corner for more information about that.
Well, hey thanks Sowmiya for sending that question in. Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
[ ♫ ] Tips and tricks get your spreadsheet fix from MrExcel.com [ ♪ ]
Highlight the top row for each ID. Hey welcome back to the MrrExcel netcast.
I am Bill Jelen. A question today from Sowmiya on YouTube.
She has several different ids.
ID1 has a lead count of 6, 9, and 10 on different dates.
ID2 has lead count of fifteen ten and nine in different dates.
She wants to highlight the the entire row with the top lead count for each ID.
So this is the formula that i sent. Sowmiya says "No I have 250 of these".
I get it.
When you are creating conditional formatting, the way to do it is don't try and build it in the conditional formatting dialog box.
Build it outside the Conditional Formatting Dialog Box. So the formula here.
Let's make it larger.
I have data today in rows 2 through 23. If you have 250 IDs then your data is larger.
Adjust the formula references to match the size of your data. So the MAXIFS.
We are going to look through all of the lead counts in B2 to B23.
This is what we're taking the Max of. The criteria is look through A2 to A23.
Notice that there's 4 dollar signs in $B$2:$B$23.
Then there are 4 dollar signs in $A$2:$A$23. Then the most important part is the third argument.
When you are creating conditional formatting you have to be writing a formula as if it applies to just the cell that you are in. In the third argument, you want to say always look in column $A.
But the 2 has no dollar sign! That means that it's allowed to move to other rows.
So imagine as this gets copied down.
It's going to be pointing at different rows. The same thing for $B2 with a single dollar sign.
We will say from among all of the things with the Lead ID of 1. If this item is the Max.
So if $B2, the item in this row is the max.
And here we will just take a look at this formula as we copy it down.
So we come down here. See in the formula the $B$2 to $B$23 stays.
The $A$2 to $A$23 stays. But the $A$2 is changed to $A11 because there's no dollar sign before the row.
And the $B2 changes to $B11. Once you have that formula working in the cells.
Then you are going to put the cell in edit mode by pressing F2. Select all of the characters in the cell.
Do not copy the cell.
You have to copy it either from the Formula Bar or in edit mode. Ctrl+C to copy.
Clear the rules. Select all of your data.
Make sure that A2 is the Active Cell. Select Conditional Formatting then New Rule.
Use a Formula to Determine Which Cells to Format.
Then Ctrl+V to paste. Be very careful in this box. If there is a mistake.
If you press the left arrow key.
Excel is going to start adding cell references. This happens because down here we are in Enter mode.
If you press F2 then you are in Edit mode. You can move left and right in the box without a problem.
Then click Format. Choose whatever format you want.
I will choose a nice light color like that. Click OK. Click OK. And now for ID1.
21 is the largest. And the whole row gets highlighted.
These don't have to be in any particular sequence.
It can be in date order and it's still going to work. Let's change 1.
Take this cell for ID1 and change it to 99 so it's the largest. See, it changes which row is highlighted.
You don't need the formulas in column D.
You just used those as a helper to help you build the formula. And make sure it's working before it goes in. Alright Sowmiya.
Hopefully that helps.
Please for everyone and Sowmiya subscribe and ring that bell.
I am trying to get a better Merchandise experience down below.
My new book MrExcel 2020 Seeing Excel Clearly.
Click the "i" in the top right hand corner for more information about that.
Well, hey thanks Sowmiya for sending that question in. Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
[ ♫ ] Tips and tricks get your spreadsheet fix from MrExcel.com [ ♪ ]
