MrExcel's Learn Excel #614 - Filter Most Recent

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Mar 31, 2009.
Alicia and George have sent in similar questions; George asks how can I filter a data set to one record per customer? Alicia had a similar question but specified that she wanted only the most recent record for each customer. If you are allowed to sort the data, Episode 614 will show you how to solve this problem.

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!
maxresdefault.jpg


Transcript of the video:
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question, actually two questions that are very similar.
One sent in by George and one sent in by Alicia.
George said hey, I have a huge database.
I have 20,000 records in this database and basically, I want to keep just one record from each customer.
So I have a column with customer number. I just want to see each customer once.
But I don't want just the customers, I want the entire record because it has some information about that customer.
Maybe their address or in George's case, It was a vehicle VIN number.
And then Alicia said. Hey, I have a huge database and I want to keep just the most recent record.
How would I keep just the most recent record?
Well today, we're going to talk about what to do, if we're allowed to sort this data.
If we're allowed to sort the data then what I'm going to do first in order to Solve Alicia's problem is sort the data, "Descending" based on dates that we have the most recent record at the top and then I'm going to sort the data "Ascending" based on customer.
When I add a new column over here and call them I called "keep" and basically this will be a little IF statement that says. If this is the first time I'm seeing this customer, then I want to keep the record otherwise, I don't so =IF this customer is not equal to the previous customer then true, otherwise False.
Now I'll double click the fill handle to copy that down to all of my rows.
And you'll see here for AIG that we have true for the first record, false for the next three records.
Now let's convert this to values, remember the fast way to convert this to values is to right click on the right edge, drag right, drag left, let go "Copy Here as Values Only" and I'll turn on the filter.
"Data" "Filter" "Autofilter" and say in the keep column. I just want to see the true records.
All right now. I can very easily, copy this data set.
Ctrl C to copy.
Go to a brand new spreadsheet.
Ctrl V and I have just one occurrence of every customer plus, I have only the most recent occurrence of each customer because I sorted by date.
So there you have it.
Thanks for stopping by. I will see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top