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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920