Microsoft Excel Tutorial - Dueling Excel - Keep The Last Duplicate.

Welcome back to another episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head to head to solve your Excel problems. In this episode, we tackle the issue of keeping the last duplicate in a large dataset. This question was sent in by Tony from Atlanta, who also shared a great tip about trying the milkshakes at a gas station in Ohio. Thanks, Tony!

In this scenario, we have a dataset of 7,000 rows with employee names and records. Sometimes, an employee may show up twice in the dataset, but we only want to keep the last record for each employee. The Remove Duplicates function in Excel only keeps the first record, so we need to find a way to keep the last one. Mike and I both have different approaches to solving this problem, so let's see who comes out on top.

Mike's solution involves adding an extra column and using the COUNTIF function to count the number of occurrences for each employee. He then uses an IF function to check if the record above is not equal to the one below, indicating a duplicate. He then filters for the records that are true and copies and pastes them onto a new sheet. This is a great solution for those who are comfortable with using formulas and filters in Excel.

My solution, on the other hand, involves using the Remove Duplicates function in a different way. By sorting the dataset in descending order by the end date, the latest record for each employee will be at the top. Then, by using the Remove Duplicates function and selecting only the employee name column, we can easily remove the duplicates and keep the last record for each employee. This method is quick and easy, but it may not work for those using older versions of Excel.

So, which method do you think is better? Watch the video to see both solutions in action and let us know in the comments which one you prefer. And don't forget to subscribe to our channels for more Excel tips and tricks. Thanks for watching and we'll see you in the next Dueling Excel podcast!

Buy Bill Jelen's latest Excel book: [ Ссылка ]

You can help my channel by clicking Like or commenting below: [ Ссылка ]


Table of Contents:
(00:00) Keep the Last Duplicate
(00:31) Using the COUNTIF function
(01:19) Using the IF function
(03:11) Filtering the data
(04:41) Alternative solution using Remove Duplicates
(05:30) 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:
Copy and paste values in Excel
COUNTIF function
Excel formula
Filter data in Excel
IF function
ISNONTEXT function
Keep the Last Duplicate
Remove Duplicates
Remove duplicates in Excel
Sort data in Excel


Join the MrExcel Message Board discussion about this video at [ Ссылка ]

Microsoft Excel Duel
Tony needs to keep the last duplicate record and throw out the earlier ones. In Episode #1464 Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen demonstrate competing methods to get this issue solved.

Dueling Excel Podcast #90...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]

and

"Learn Excel from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!

"The Learn Excel from MrExcel Podcast Series"

свернуть