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