Conditional Formatting offers the ability to mark duplicates or uniques, but it sort of missed the boat. Today, in Episode #1326, Bill looks at how to use a formula-based conditions with VBA to fill voids left by Conditional Formatting.
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, 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 2 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 is 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.
We'll do what we want, what we really want is, what I call wishful thinking over here and 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 using 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 count if is 1, then this is the first time that's occurred.
All right! Now, let's take a look at how to do that in VBA, will switch over to VBA here and will take a look at the code.
So, where is the first 2, were pretty easy here just using Dupe Unique equal to xl duplicates, or Dupe Unique equal to xl Unique 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 formula 1, that formula has to be the same formula that would set up.
So, again relative the very first cell here, E2 this format has to refer to formula that will work in E2.
Now, they'll replicate it because I used a clever use of dollar signs here and no dollar signs 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 really be nice if Microsoft would offer this as one of those choices, you don't know what they would call it if they call it Bill Jelen Unique or something like that, who knows, so useful unique.
I don't know, I mean...
But you get the point here, we're trying to get a list of every item but only one of every item not the second, not the third...
Hey, I want to thank you for stopping by, 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, 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 2 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 is 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.
We'll do what we want, what we really want is, what I call wishful thinking over here and 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 using 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 count if is 1, then this is the first time that's occurred.
All right! Now, let's take a look at how to do that in VBA, will switch over to VBA here and will take a look at the code.
So, where is the first 2, were pretty easy here just using Dupe Unique equal to xl duplicates, or Dupe Unique equal to xl Unique 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 formula 1, that formula has to be the same formula that would set up.
So, again relative the very first cell here, E2 this format has to refer to formula that will work in E2.
Now, they'll replicate it because I used a clever use of dollar signs here and no dollar signs 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 really be nice if Microsoft would offer this as one of those choices, you don't know what they would call it if they call it Bill Jelen Unique or something like that, who knows, so useful unique.
I don't know, I mean...
But you get the point here, we're trying to get a list of every item but only one of every item not the second, not the third...
Hey, I want to thank you for stopping by, see you next time for another netcast from MrExcel.
