Look Who it Was Random video

Excel - Learn How to Find the Most Recent Record Per Customer in Excel Without Sorting - Episode 615

544 views · Published 31 March 2009 · 3:04 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Learn How to Find the Most Recent Record Per Customer in Excel Without Sorting.

Welcome back to the MrExcel netcast, where we provide you with helpful tips and tricks for using Excel. In this episode, we're revisiting a question from our viewers George and Alicia, who both had a large data set with 20,000 rows and wanted to filter it to only show one record per customer. But, Alicia also wanted to make sure that the record shown was the most recent one for each customer. In yesterday's podcast, I showed a solution that involved sorting the data set, but today we're going to tackle the problem without sorting.

To do this, we're going to use a combination of the MAX and IF functions. First, we'll use the MAX function to find the largest date for each customer. Then, we'll use the IF function to check if the customer in each row matches the customer in the MAX date row. If it does, we'll return the corresponding date, and if not, we'll return a 0. This will create an array in memory with the largest date for each customer and 0s for all other records.

But, in order for this formula to work, we need to use a special type of formula called an Array Formula. To do this, we'll hold down the control and shift keys while hitting enter. This will tell Excel to treat the formula as an array and calculate it accordingly. As we copy the formula down, it will automatically adjust to find the largest date for each customer.

Now, we can use the AutoFilter feature to filter the data set based on the results of our formula. By filtering the rightmost column to only show "True" values, we can see the most recent record for each customer without having to sort the data. This is a more complex solution, but it allows us to keep the data in its original order. Remember, to use this formula, you'll need to hold down control and shift while hitting enter. Thanks for watching and be sure to tune in for more helpful tips and tricks from MrExcel.

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/


In todays podcast, I address the same question from podcast 614, but assume that the dataset can not be sorted. This requires a powerful array formula to find the most recent date for each customer. Episode 615 shows you how.

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) Filter to most recent record per customer
(00:33) Simplifying Assumption
(00:43) Building the Formula
(01:41) Special Formula: Array Formula
(02:02) Using the Formula
(02:44) 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:
Array formula in Excel for finding maximum value in a range
Excel formula to find the largest date for each customer
How to handle large datasets in Excel (20,000 rows)
How to use IF function in Excel to compare values
Keyboard shortcuts in Excel (e.g., control+shift+enter)
MrExcel netcast episode on data analysis techniques in Excel
Sorting data in Excel for analysis and reporting
Step-by-step tutorial on filtering data in Excel using AutoFilter
Techniques for manipulating and analyzing customer data in Excel
Tips for optimizing data analysis in Excel
Tricks for working with complex formulas in Excel
YouTube video on Finding the most recent record per customer in Excel


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

More from this channel