Conditional Formatting offers the ability to mark duplicates or uniques, but I think they missed the boat. This episode looks at how to use a formula-based condition to solve the problem
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Excel VBA chapter 15 - Marking Duplicates!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
In Excel 2007 they added these new conditional formatting rules, including something called Duplicate Values.
I was pretty excited about this when I saw it because they offer the chance to mark either duplicates or uniques.
But, you know, in my humble opinion, they got this wrong, they didn't do what was really useful.
So here's what would happen if you chose duplicates, and you'll see that 17 is selected because 17 appears two times, and 11 is highlighted because 11 appears two times.
And let's take a look at unique values over in column C, and you see that it's the opposite.
What they're saying is "Hey, 12 that's being highlighted here is unique because it only occurs one time in the list." But to me this is not really the list of unique values, what we really want for unique values is, we want the first 17, the first 11, the first 7, but not the second set, and neither of those built-in items will do what we want.
What we really want is what I call wishful thinking.
Over here I made a little macro to do that.
In "Wishful", the first 17 is highlighted because that is a unique value, that 17, the second 17 isn't because it's a repeat, 11, 7 and so on.
And so in this case, the red cells are the complete list of truly every value occurring once, which I think is really what we need.
Now to do this is much harder than just choosing duplicates, we have to use a formula version.
So we say "Use a formula to determine which cells to format", that formula has to be written relative to the first active cell, so in this range E2 is the first active cell.
We say "Hey, always go from E2 down to the current cell, see if it's equal to the current cell.
And if that COUNTIF is 1, then this is the first time that's occurred." Alright, now let's take a look at how to do that in VBA, switch over to VBA here, and take a look at the code.
So whereas the first two were pretty easy here just using DupeUnique = xlDuplicates, or DupeUnique = xlUnique, to do the one that I think is actually useful, we have to do where we're adding a condition as an expression, and then pass an argument, a Formula1.
That formula has to be the same formula that it would set up.
So again, relative to very first cell here, E2, this formula has to refer to a formula that will work in E2.Now they'll replicate it, and because I used a clever use of $ here, and no $, it will change as we go down through the various cells.
But to set this up in VBA, certainly a little bit easier, perhaps then figuring out every time, to get that in there as a macro.
It would be really nice if Microsoft would offer this as one of those choices, don't know what they would call it, maybe they'd call it "Bill Jelen Unique" or something like that, who knows so, "Useful Unique", I don't know.
But you get the point here, we're trying to get a list of every item, but only one of every item.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Excel VBA chapter 15 - Marking Duplicates!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
In Excel 2007 they added these new conditional formatting rules, including something called Duplicate Values.
I was pretty excited about this when I saw it because they offer the chance to mark either duplicates or uniques.
But, you know, in my humble opinion, they got this wrong, they didn't do what was really useful.
So here's what would happen if you chose duplicates, and you'll see that 17 is selected because 17 appears two times, and 11 is highlighted because 11 appears two times.
And let's take a look at unique values over in column C, and you see that it's the opposite.
What they're saying is "Hey, 12 that's being highlighted here is unique because it only occurs one time in the list." But to me this is not really the list of unique values, what we really want for unique values is, we want the first 17, the first 11, the first 7, but not the second set, and neither of those built-in items will do what we want.
What we really want is what I call wishful thinking.
Over here I made a little macro to do that.
In "Wishful", the first 17 is highlighted because that is a unique value, that 17, the second 17 isn't because it's a repeat, 11, 7 and so on.
And so in this case, the red cells are the complete list of truly every value occurring once, which I think is really what we need.
Now to do this is much harder than just choosing duplicates, we have to use a formula version.
So we say "Use a formula to determine which cells to format", that formula has to be written relative to the first active cell, so in this range E2 is the first active cell.
We say "Hey, always go from E2 down to the current cell, see if it's equal to the current cell.
And if that COUNTIF is 1, then this is the first time that's occurred." Alright, now let's take a look at how to do that in VBA, switch over to VBA here, and take a look at the code.
So whereas the first two were pretty easy here just using DupeUnique = xlDuplicates, or DupeUnique = xlUnique, to do the one that I think is actually useful, we have to do where we're adding a condition as an expression, and then pass an argument, a Formula1.
That formula has to be the same formula that it would set up.
So again, relative to very first cell here, E2, this formula has to refer to a formula that will work in E2.Now they'll replicate it, and because I used a clever use of $ here, and no $, it will change as we go down through the various cells.
But to set this up in VBA, certainly a little bit easier, perhaps then figuring out every time, to get that in there as a macro.
It would be really nice if Microsoft would offer this as one of those choices, don't know what they would call it, maybe they'd call it "Bill Jelen Unique" or something like that, who knows so, "Useful Unique", I don't know.
But you get the point here, we're trying to get a list of every item, but only one of every item.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
