Tony needs to keep the last duplicate record and throw out the earlier ones. In Episode #1464 Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen demonstrate competing methods to get this issue solved.
Dueling Excel Podcast #90...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Dueling Excel Podcast #90...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Transcript of the video:
Hey, welcome back.
It's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel and will be joined by Mike Girvin from Excel Is Fun.
This is episode 90, Keep the Last Duplicate.
All right, Mike. Hey, this is a great question.
This is sent in by Tony. Tony is from Atlanta.
It's funny. Tony, you know, lives 500 miles from me, but he says, "Oh you're from Akron.
You got to stop by the gas station and try their milkshakes." I'm sure enough that the Sheetz Gas Station up here in Ohio, they have great milkshakes.
So, you wouldn't think it's true, but, so thanks to Tony for the great milkshake trick.
Now, we've changed Tony's data here.
He has a huge data set, 7,000 rows and it has employees, employees.
And sometimes an employee shows up twice, it's sorted by date so, that the latest record for that employee is last.
Tony says, "We need a way to keep just the last version." Remove Duplicates, gives us the first one.
That's not what we want. We want the last one.
All right! So Mike, I'm letting you go first.
Let's see what you have?
Mike: Thanks MrExcel.
Hey, so we have Duplicates and we want the last one.
So, there's two records here and we wanna extract the records...
Where if there's Duplicates, the last ones.
If there's no Duplicates, we want to just extract that.
Well, I'm gonna go ahead and add an extra column and then use the filter.
So, I'm gonna say, "Last." Now, let's start out.
I'm gonna see if I can count how many there are, of each one.
So, I'm going to go equals COUNTIF.
And the range, well, I'm gonna click right on the first cell in that Name column and hit the Colon, Shift+Colon, comma and then click back over here, close parentheses.
Now, I'm gonna lock this, A2.
This is an expandable range, that one's locked, this one's not.
So, as it goes down the range will expand.
So right now, if for example, when you get down to Fannie, it'll only be seen one Fannie.
But when it gets down to the second one because this range is expanding, it'll put a 2 there. So, are you ready?
So, the criteria is always gonna be a relative cell reference, Control+Enter and then I'm going to double click and send it down.
All right, so you see, I get a 2 here for Fannie, down here and this is the third one.
All right, now, all right, this column is sorted, right?
So, everything's sorted.
So, I'm gonna take advantage of that. When I get to here, I'm gonna notice that the one below is different.
All right. So, I'm gonna use an AND function.
I'm not... Not an AND, an IF.
I'm gonna say IF, this right here, that's a relative cell reference forward to my left is not, that's less than greater than, not equal to the one below, comma, in that case.
So, for example, when we get down to BARKER, FANNIE, these are not equal.
So, I'm going to put for the second FANNIE, the 2.
Notice, when I get to a recurrence of just one single person, these are automatically gonna be different or not equal.
All right. So, that's the value of two, comma the value of False.
I'm going to put double quotes, close parentheses.
Control+Enter. And then I'm gonna double click and send this down.
So now, oh! I have blanks, for ones that I don't want.
Now, I'm going to simply, I want to True or False in this column.
I'm going to come right here and say, "I'm interested in the ones that are either a number or not text." Now, usually we use ISNUMBER, but I'm gonna use ISNONTEXT.
That's... I mean, it just means it's not text here.
You could just as easily use ISNUMBER, In fact now, we have a False there, False there because those ones are the first listings down.
Here, we have these two Falses for NORMA.
Right, now, I'm gonna turn on the filter.
I'm going to click in a single cell in the data set.
There's empty cells all the way around and Control+Shift+L or you just use that button right there and now, I'm gonna use the filter.
I wanna see everything that's True.
So, I'm going to uncheck False.
Immediately, I could see over here there's blue which means, it's filtered.
I can also see there's missing numbers.
Now, I'm going to click in a single cell and Control+Asterisks to highlight them all, Control+C.
Notice, when you have a filter and you highlight everything and Control+C, the dancing ants are going around just the visible cells.
So, now I come over to my output sheet and Control+V and then I could point to my smart tag and say, "Hey, give me the column ones." And there's all of the last occurrence of any duplicates.
All right, throw back to MrExcel.
Bill: Hey, that's wild!
The ISNONTEXT, I don't think I've ever used that, right?
Now Mike, my first reaction was the formula.
Check and see if A2 is not equal to A3, and then I wasn't gonna filter it.
So, I was just gonna Copy, Paste values, sort all the Trues or the ones l'd keep, but then I said, "Okay wait, Remove Duplicates, is really a fast way to solve this problem." The problem with Remove Duplicates is, it's keeping the first of each person and throwing out the rest.
So, I said, "What if we just came here to End Date?" Sort Z to A, that way the latest record for each person shows up on top.
Now, Remove Duplicates, Unselect All, Name and OK.
63 values removed. 531 unique values remain.
We end up with just the oldest or I mean the the latest dated record for each person because we brought those naturally to the top.
Now so, if you have Excel 2007 or newer, it might be a good way to go.
Otherwise, the formula method will work.
All right! Well hey, I wanna to thank you for stopping by.
We'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun.
It's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel and will be joined by Mike Girvin from Excel Is Fun.
This is episode 90, Keep the Last Duplicate.
All right, Mike. Hey, this is a great question.
This is sent in by Tony. Tony is from Atlanta.
It's funny. Tony, you know, lives 500 miles from me, but he says, "Oh you're from Akron.
You got to stop by the gas station and try their milkshakes." I'm sure enough that the Sheetz Gas Station up here in Ohio, they have great milkshakes.
So, you wouldn't think it's true, but, so thanks to Tony for the great milkshake trick.
Now, we've changed Tony's data here.
He has a huge data set, 7,000 rows and it has employees, employees.
And sometimes an employee shows up twice, it's sorted by date so, that the latest record for that employee is last.
Tony says, "We need a way to keep just the last version." Remove Duplicates, gives us the first one.
That's not what we want. We want the last one.
All right! So Mike, I'm letting you go first.
Let's see what you have?
Mike: Thanks MrExcel.
Hey, so we have Duplicates and we want the last one.
So, there's two records here and we wanna extract the records...
Where if there's Duplicates, the last ones.
If there's no Duplicates, we want to just extract that.
Well, I'm gonna go ahead and add an extra column and then use the filter.
So, I'm gonna say, "Last." Now, let's start out.
I'm gonna see if I can count how many there are, of each one.
So, I'm going to go equals COUNTIF.
And the range, well, I'm gonna click right on the first cell in that Name column and hit the Colon, Shift+Colon, comma and then click back over here, close parentheses.
Now, I'm gonna lock this, A2.
This is an expandable range, that one's locked, this one's not.
So, as it goes down the range will expand.
So right now, if for example, when you get down to Fannie, it'll only be seen one Fannie.
But when it gets down to the second one because this range is expanding, it'll put a 2 there. So, are you ready?
So, the criteria is always gonna be a relative cell reference, Control+Enter and then I'm going to double click and send it down.
All right, so you see, I get a 2 here for Fannie, down here and this is the third one.
All right, now, all right, this column is sorted, right?
So, everything's sorted.
So, I'm gonna take advantage of that. When I get to here, I'm gonna notice that the one below is different.
All right. So, I'm gonna use an AND function.
I'm not... Not an AND, an IF.
I'm gonna say IF, this right here, that's a relative cell reference forward to my left is not, that's less than greater than, not equal to the one below, comma, in that case.
So, for example, when we get down to BARKER, FANNIE, these are not equal.
So, I'm going to put for the second FANNIE, the 2.
Notice, when I get to a recurrence of just one single person, these are automatically gonna be different or not equal.
All right. So, that's the value of two, comma the value of False.
I'm going to put double quotes, close parentheses.
Control+Enter. And then I'm gonna double click and send this down.
So now, oh! I have blanks, for ones that I don't want.
Now, I'm going to simply, I want to True or False in this column.
I'm going to come right here and say, "I'm interested in the ones that are either a number or not text." Now, usually we use ISNUMBER, but I'm gonna use ISNONTEXT.
That's... I mean, it just means it's not text here.
You could just as easily use ISNUMBER, In fact now, we have a False there, False there because those ones are the first listings down.
Here, we have these two Falses for NORMA.
Right, now, I'm gonna turn on the filter.
I'm going to click in a single cell in the data set.
There's empty cells all the way around and Control+Shift+L or you just use that button right there and now, I'm gonna use the filter.
I wanna see everything that's True.
So, I'm going to uncheck False.
Immediately, I could see over here there's blue which means, it's filtered.
I can also see there's missing numbers.
Now, I'm going to click in a single cell and Control+Asterisks to highlight them all, Control+C.
Notice, when you have a filter and you highlight everything and Control+C, the dancing ants are going around just the visible cells.
So, now I come over to my output sheet and Control+V and then I could point to my smart tag and say, "Hey, give me the column ones." And there's all of the last occurrence of any duplicates.
All right, throw back to MrExcel.
Bill: Hey, that's wild!
The ISNONTEXT, I don't think I've ever used that, right?
Now Mike, my first reaction was the formula.
Check and see if A2 is not equal to A3, and then I wasn't gonna filter it.
So, I was just gonna Copy, Paste values, sort all the Trues or the ones l'd keep, but then I said, "Okay wait, Remove Duplicates, is really a fast way to solve this problem." The problem with Remove Duplicates is, it's keeping the first of each person and throwing out the rest.
So, I said, "What if we just came here to End Date?" Sort Z to A, that way the latest record for each person shows up on top.
Now, Remove Duplicates, Unselect All, Name and OK.
63 values removed. 531 unique values remain.
We end up with just the oldest or I mean the the latest dated record for each person because we brought those naturally to the top.
Now so, if you have Excel 2007 or newer, it might be a good way to go.
Otherwise, the formula method will work.
All right! Well hey, I wanna to thank you for stopping by.
We'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun.
