A question from YouTube on how to write a macro to mark duplicates in each row where duplicates over the value of 5 are marked in red and under 5 are marked in blue. This can be done without a macro, using two formula-based conditional formatting rules. However - the formula will change depending on if you want to mark both halves of the duplicate or just the 2nd occurrence of the duplicate.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1777: Mark Duplicates >5 In Red, <5 In Blue.
Hey. Welcome back to the MrExcel netcast.
I’m Bill Jelen. Well, today's question came in from YouTube and they were actually looking for a macro to do this.
In each row, we have numbers from B through J, and we want to mark the duplicates. If they're higher than 5, use red, lower than 5, use blue, and it turns out we don't have to use a macro for this at all. We can do this with 2 conditional formatting rules, but the formula here is going to be rather tricky, because we're going to select this entire range, and you have to look and figure out what the top-left corner cell is. That’s cell B6, right, and we're going to write a formula that works for that cell but the formula has to have the right combination of $ signs so that way it'll work when copied throughout the entire range, alright?
So, one thing we're going to do for the, what is it, red, we’re going to look and see if B6, the current cell, is > 5, that's going to be one check. We're going to have an AND function to check 2 things, and then we're also going to do a COUNTIF from $ sign B6, in other words, always starting at column B of this row and look all the way out to $ sign J6, always J6 or column J of this row, and compare that to the current value, B6, and if there are more than 1, then mark the cell.
Okay. Now, that assumes we want to mark both duplicates in the color, so 13 and 13, we want to mark them both, then that will work, but I'm not sure from the question. When I am looking for duplicates, I only want to mark the problem cell, not both halves of the duplicate. I only want to mark the one out here, you know. So, maybe, you know, we are applying checks to an invoice or something like that, I don't know, and I only want to mark the second one, and if that's the way we want to go, then we're always going to check from $ sign B6 but we're not going to go out to column J. We're going to go to the current column, so B6 without the $ sign, alright?
So, let's set them both up. I'll do ALT+O D for format, conditional formatting. We’ll do a NEW RULE, USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT, and it's going to be =AND because we have 2 conditions. First condition, B6 > 5, so this is going to mark 6, 7, 8, 9 and so on. If you needed 5 included here, you would use > or = to 5. ,. Alright. Second condition is our COUNTIF and we’re going to use $ sign B6 : $ sign J6 , see if it's = to no $ signs B6 is > 1. Alright. There we go. Now, the trick here, so, my data happens to start in B6. Your data might start somewhere else. It might start in B2 or B14 or something like that. So, if you're not in row 6, if the top-left corner cell isn’t in row 6, everywhere where I have a 6 here, you would have to change to your starting row, maybe 2, change that to a 2, change that to a 2, change that to a 2, and so on. [ =AND(B6>5,COUNTIF($B6:$J6,B6)>1) ] Alright. So, we want red. You can either choose a red font like this or a red fill like this, or both. Your call. I’m going to put them both in there, click OK, alright, and what we can actually apply here, and you'll see that anytime that there is a duplicate > 5, they're both being marked. Alright. Now, we're going to add…actually, I'm going to go in and edit this rule. I'm going to copy this to the clipboard, CONTROL+C, click OK.
I'm going to add a new rule. USE A FORMULA, I'm going to paste that exact same formula in, but this time I'm going to say if it's < or = to 5. These are the ones we want to mark as blue, so we use a light blue fill and a dark blue font, click OK, click OK, click OK, alright, and so, now, here, this -4 is a duplicate, the -5 is a duplicate, -3 is a duplicate, 16 is a duplicate above 5 so we get red. [ =AND(B6<=5,COUNTIF($B6:$J6,B6)>1) ] Alright. So, now, you see this is marking both duplicates. I'm not sure that that’s exactly what we want so I'm going to make a copy of this sheet, and we'll go in and edit these formulas. So, ALT+O D. That brings us back to our rules manager. We’ll edit this rule, and if we only want to mark the second, third, fourth duplicates in each row, then this $ sign J. You see that. I was in point mode, I need to press F2 to get out of point mode. Down here, you'll see in the lower left hand corner, we switched to edit mode. I need to change that $ sign J to just no $ sign B, click OK, and then this one, we’ll edit the rule, get rid of the $ sign J, change it to a B, click OK, click OK. [ =AND(B6<=5,COUNTIF($B6:B6,B6)>1) ], [ =AND(B6>5,COUNTIF($B6:B6,B6)>1) ] Very subtle change there. So, now it's marking the second one, and if there would be a third one, let's say there was a 13 here, it would mark that one as well. So, the first one doesn't get marked but then the duplicates in each row get marked.
So, a couple of different ways to go there using conditional formatting, just making sure that we get those $ signs right so that way, as the formula expands…you know here's an example. If we would go down to this, for example, cell G14, what the rule is doing as it gets copied down to row 14, because we didn't put $ signs before any of the row numbers, the row 6 is allowed to change to row 14. So, we're saying, hey, check to see if G14 is > 5 and then check to see if column B of this row, row 14, to column J of this row, row 14, is = to G14, yeah, is > 1. That's why I'm very careful here, very deliberate, about where those $ signs go. So, that way, as the formula gets copied down and over, that range continues to be the correct range, and if we're just trying to mark the second one, we want to go from column B of this row to the current column, current row, and see if it's = to current column, current row.
Well, hey. I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1777: Mark Duplicates >5 In Red, <5 In Blue.
Hey. Welcome back to the MrExcel netcast.
I’m Bill Jelen. Well, today's question came in from YouTube and they were actually looking for a macro to do this.
In each row, we have numbers from B through J, and we want to mark the duplicates. If they're higher than 5, use red, lower than 5, use blue, and it turns out we don't have to use a macro for this at all. We can do this with 2 conditional formatting rules, but the formula here is going to be rather tricky, because we're going to select this entire range, and you have to look and figure out what the top-left corner cell is. That’s cell B6, right, and we're going to write a formula that works for that cell but the formula has to have the right combination of $ signs so that way it'll work when copied throughout the entire range, alright?
So, one thing we're going to do for the, what is it, red, we’re going to look and see if B6, the current cell, is > 5, that's going to be one check. We're going to have an AND function to check 2 things, and then we're also going to do a COUNTIF from $ sign B6, in other words, always starting at column B of this row and look all the way out to $ sign J6, always J6 or column J of this row, and compare that to the current value, B6, and if there are more than 1, then mark the cell.
Okay. Now, that assumes we want to mark both duplicates in the color, so 13 and 13, we want to mark them both, then that will work, but I'm not sure from the question. When I am looking for duplicates, I only want to mark the problem cell, not both halves of the duplicate. I only want to mark the one out here, you know. So, maybe, you know, we are applying checks to an invoice or something like that, I don't know, and I only want to mark the second one, and if that's the way we want to go, then we're always going to check from $ sign B6 but we're not going to go out to column J. We're going to go to the current column, so B6 without the $ sign, alright?
So, let's set them both up. I'll do ALT+O D for format, conditional formatting. We’ll do a NEW RULE, USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT, and it's going to be =AND because we have 2 conditions. First condition, B6 > 5, so this is going to mark 6, 7, 8, 9 and so on. If you needed 5 included here, you would use > or = to 5. ,. Alright. Second condition is our COUNTIF and we’re going to use $ sign B6 : $ sign J6 , see if it's = to no $ signs B6 is > 1. Alright. There we go. Now, the trick here, so, my data happens to start in B6. Your data might start somewhere else. It might start in B2 or B14 or something like that. So, if you're not in row 6, if the top-left corner cell isn’t in row 6, everywhere where I have a 6 here, you would have to change to your starting row, maybe 2, change that to a 2, change that to a 2, change that to a 2, and so on. [ =AND(B6>5,COUNTIF($B6:$J6,B6)>1) ] Alright. So, we want red. You can either choose a red font like this or a red fill like this, or both. Your call. I’m going to put them both in there, click OK, alright, and what we can actually apply here, and you'll see that anytime that there is a duplicate > 5, they're both being marked. Alright. Now, we're going to add…actually, I'm going to go in and edit this rule. I'm going to copy this to the clipboard, CONTROL+C, click OK.
I'm going to add a new rule. USE A FORMULA, I'm going to paste that exact same formula in, but this time I'm going to say if it's < or = to 5. These are the ones we want to mark as blue, so we use a light blue fill and a dark blue font, click OK, click OK, click OK, alright, and so, now, here, this -4 is a duplicate, the -5 is a duplicate, -3 is a duplicate, 16 is a duplicate above 5 so we get red. [ =AND(B6<=5,COUNTIF($B6:$J6,B6)>1) ] Alright. So, now, you see this is marking both duplicates. I'm not sure that that’s exactly what we want so I'm going to make a copy of this sheet, and we'll go in and edit these formulas. So, ALT+O D. That brings us back to our rules manager. We’ll edit this rule, and if we only want to mark the second, third, fourth duplicates in each row, then this $ sign J. You see that. I was in point mode, I need to press F2 to get out of point mode. Down here, you'll see in the lower left hand corner, we switched to edit mode. I need to change that $ sign J to just no $ sign B, click OK, and then this one, we’ll edit the rule, get rid of the $ sign J, change it to a B, click OK, click OK. [ =AND(B6<=5,COUNTIF($B6:B6,B6)>1) ], [ =AND(B6>5,COUNTIF($B6:B6,B6)>1) ] Very subtle change there. So, now it's marking the second one, and if there would be a third one, let's say there was a 13 here, it would mark that one as well. So, the first one doesn't get marked but then the duplicates in each row get marked.
So, a couple of different ways to go there using conditional formatting, just making sure that we get those $ signs right so that way, as the formula expands…you know here's an example. If we would go down to this, for example, cell G14, what the rule is doing as it gets copied down to row 14, because we didn't put $ signs before any of the row numbers, the row 6 is allowed to change to row 14. So, we're saying, hey, check to see if G14 is > 5 and then check to see if column B of this row, row 14, to column J of this row, row 14, is = to G14, yeah, is > 1. That's why I'm very careful here, very deliberate, about where those $ signs go. So, that way, as the formula gets copied down and over, that range continues to be the correct range, and if we're just trying to mark the second one, we want to go from column B of this row to the current column, current row, and see if it's = to current column, current row.
Well, hey. I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
