Excel - Remove Duplicates Based on Start of Cell - Excel - Episode 1532

2,846 views · Published 23 March 2012 · 6:52 · Indexed 31 October 2026

Channel: MrExcel.com · 2012 · Education

Watch on YouTube

Microsoft Excel Tutorial: How can I Remove Duplicates IF the first two words -before the first two commas- are the same?

Welcome to another exciting episode of Dueling Excel, live from Highline Community College. I'm Bill Jelen from MrExcel, and with me is the brilliant Mike Girvin from Excel Is Fun. Today, we have a question from Tina, who wants to use the Remove Duplicates function but only if the first two elements between the commas are duplicates. Mike has a formula for this, but I'm hoping for an easier solution. Let's see what he has come up with.

Mike's formula involves using the SUBSTITUTE function to insert a different character between the first two elements, and then using the SEARCH function to find the position of that character. He then uses the LEFT function to extract the first element and counts the number of times it appears in the range. If it appears more than once, the formula returns TRUE, indicating that the row should be deleted. While this is a clever solution, it may be a bit complicated for some users.

But fear not, because I have a simpler solution. By using the Text to Columns feature and then the Remove Duplicates function, we can easily remove the duplicate rows based on the first two elements. This method is quick and efficient, and it's a great example of how there can be multiple ways to solve a problem in Excel.

I have to give credit to Mike for coming up with most of the problems for our Dueling Excel Podcast. But that's the beauty of Excel - everyone has their own unique approach to solving problems. And in the end, we all learn something new and improve our skills.

So there you have it, two different solutions to the same problem. Which one do you prefer? Let us know in the comments below. And don't forget to like and subscribe to our channel for more exciting Excel tips and tricks. Thanks for watching, and we'll see you in the next episode of Dueling Excel!"

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/

Table of Contents:
(00:00) Introduction by Bill and Mike
(00:12) Tina's question about using Remove Duplicates
(00:38) Mike's formula to solve the problem
(01:14) Bill's disbelief and discussion about the formula
(03:13) Mike's explanation of the formula and its components
(04:20) Bill's alternative solution using Text to Columns and Remove Duplicates
(06:13) Conclusion and thanks to Mike for providing the problem
(06:42) 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:
Concatenate
COUNTIF function
Delete hidden rows
Filter By Selection
Formula to solve problem
Remove Duplicates
Remove Duplicates with multiple columns
SEARCH function
SUBSTITUTE function
Text to Columns
True/False formula


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

Today, in yet another 'Almost Live' Dueling Excel with Mike 'ExcelisFun' girvin and Bill 'MrExcel' Jelen, Mike and Bill offer solutions. 
Now...Bill doesn't believe that this task can be completed with a Formula and Mike says that it can. What is your synopsis? Following along with Episode #1532 to see how to remove those dupes.

Dueling Excel Podcast #101...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 2007 through Excel 2010 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"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!

More from this channel