How can you find the most recent record for each customer? The method I proposed in Episode 614 is not the fastest way. Richard from Jaguar Cars Ltd sends in a faster method. Episode 639 shows you how. The podcast will take off a couple of days for the U.S. Thanksgiving holiday and will resume on Monday.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we're going to go all the way back to episode 614, where I was trying to find a unique list of customers, but I only wanted the record from the most recent date went through a series of solutions there and I got a note from Richard Potts.
Richard is from England. He said hey you missed it there's a much easier way to go and he's right.
It's very cool.
First thing we wanna do is sort the data descending based on date, that way we automatically have the most recent record for each customer at the top and then we're going to go to Data, Filter and choose Advanced Filter.
Now, normally the Advanced Filter extends to include all of the cells in our selection, but we want to make sure that it gets just the customers.
I'm gonna change this address.
So, it ranges from D to D. I'm going to say Filter the list, In-place.
That's the default and then ask for unique records only now because we're only filtering Column D.
What's going to happen is we're only going to get each customer once and it's going to give us the first occurrence of each customer.
Of course because of all the other columns we're going to see those columns as well, and what we end up with is just the last record for each customer.
Now, we'll select this data control+C to copy, go to a new workbook control+V and Excel automatically gives us just the visible row only.
There we have it, just the last record for each customer.
Great tip thanks to Richard for send in that long.
I'll be taking one of my excel master pins and getting it to them.
Well, I'm in England.
Hey, tomorrow is thanks giving in the United States.
Typically, we have Thursday and Friday off. Now, I'm already on holiday.
I'm in England. I'm doing a couple of seminars over there, but Laura who adds to the podcast is here.
So, we're gonna close the office for a couple of days take Thursday and Friday off and the podcast will resume on Monday.
Now, if you're in the United States, I hope that you have a great thanks giving holiday.
If you're anywhere else, for example, if you're in England, I hope you showed up to the seminar today otherwise.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Hey, tomorrow in the United States is thanks giving and then the day after that is a typical holiday.
Now, I'm in England today.
Hey now, tomorrow is thanksgiving in the United States, and then the day after that is a typical holiday, holiday.
We'll be back with you on Monday. I'll actually be back in the United States.
Hey now tomorrow is thanks giving in Untied States.
So, if you're in the United States, have a great holiday if you're elsewhere I hope you stop by.
Hey, so if you're in there, it's...
All right, sorry Laura. Let's start the whole thing over.
Hey, if you're anywhere else, for example if you're in England, I hope you showed up to the seminar today.
Otherwise, we'll see you next time. Thanks for stopping by, Otherwise, thanks for stopping by we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we're going to go all the way back to episode 614, where I was trying to find a unique list of customers, but I only wanted the record from the most recent date went through a series of solutions there and I got a note from Richard Potts.
Richard is from England. He said hey you missed it there's a much easier way to go and he's right.
It's very cool.
First thing we wanna do is sort the data descending based on date, that way we automatically have the most recent record for each customer at the top and then we're going to go to Data, Filter and choose Advanced Filter.
Now, normally the Advanced Filter extends to include all of the cells in our selection, but we want to make sure that it gets just the customers.
I'm gonna change this address.
So, it ranges from D to D. I'm going to say Filter the list, In-place.
That's the default and then ask for unique records only now because we're only filtering Column D.
What's going to happen is we're only going to get each customer once and it's going to give us the first occurrence of each customer.
Of course because of all the other columns we're going to see those columns as well, and what we end up with is just the last record for each customer.
Now, we'll select this data control+C to copy, go to a new workbook control+V and Excel automatically gives us just the visible row only.
There we have it, just the last record for each customer.
Great tip thanks to Richard for send in that long.
I'll be taking one of my excel master pins and getting it to them.
Well, I'm in England.
Hey, tomorrow is thanks giving in the United States.
Typically, we have Thursday and Friday off. Now, I'm already on holiday.
I'm in England. I'm doing a couple of seminars over there, but Laura who adds to the podcast is here.
So, we're gonna close the office for a couple of days take Thursday and Friday off and the podcast will resume on Monday.
Now, if you're in the United States, I hope that you have a great thanks giving holiday.
If you're anywhere else, for example, if you're in England, I hope you showed up to the seminar today otherwise.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Hey, tomorrow in the United States is thanks giving and then the day after that is a typical holiday.
Now, I'm in England today.
Hey now, tomorrow is thanksgiving in the United States, and then the day after that is a typical holiday, holiday.
We'll be back with you on Monday. I'll actually be back in the United States.
Hey now tomorrow is thanks giving in Untied States.
So, if you're in the United States, have a great holiday if you're elsewhere I hope you stop by.
Hey, so if you're in there, it's...
All right, sorry Laura. Let's start the whole thing over.
Hey, if you're anywhere else, for example if you're in England, I hope you showed up to the seminar today.
Otherwise, we'll see you next time. Thanks for stopping by, Otherwise, thanks for stopping by we'll see you next time for another netcast from MrExcel.
