Look Who it Was Random video

Excel - Keep One Record per Customer in Excel | Sorting, Filtering, & IF Statements - Episode 614

314 views · Published 31 March 2009 · 2:29 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Keep One Record per Customer in Excel | Sorting, Filtering, & IF Statements.

Welcome back to the MrExcel netcast! In today's episode, we have not one, but two questions from our viewers, George and Alicia, that are very similar. George has a huge database with 20,000 records and wants to keep just one record from each customer. Meanwhile, Alicia also has a large database and wants to keep only the most recent record for each customer. So, how do we solve these problems? Let's find out!

If we are allowed to sort the data, then the solution is quite simple. We will first sort the data in descending order based on the dates, so that the most recent record is at the top. Then, we will sort the data in ascending order based on the customer number. This will group all the records for each customer together. Next, we will add a new column called "keep" and use an IF statement to determine if it is the first time we are seeing a particular customer. If it is, then we will keep the record, otherwise, we will mark it as false.

Once we have the "keep" column filled with true and false values, we can easily filter out the false values to only see the records we want to keep. To do this, we will convert the formula to values and then use the filter function to only show the true values in the "keep" column. This will give us a clean dataset with only one occurrence of each customer and the most recent record for each customer.

And there you have it! With just a few simple steps, we have solved both George and Alicia's problems. Thank you for tuning in to this episode of the MrExcel netcast. Don't forget to subscribe to our channel for more helpful tips and tricks for using Excel. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


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!

Table of Contents:
(00:00) Introduction 
(00:23) George's question about keeping one record per customer 
(00:36) Alicia's question about keeping the most recent record 
(00:55) Sorting the data to solve Alicia's problem 
(01:11) Using an IF statement to determine which records to keep 
(01:31) Converting to values and using the filter 
(01:53) Copying the data to a new spreadsheet 
(02:09) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Converting formulas to values
Copying and pasting data in Excel
Database management
Excel IF statement
Filtering data
Keeping one record per customer
Keeping the most recent record
Sorting data
Sorting in ascending order
Sorting in descending order


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152320/

More from this channel