Excel - Delete Prospects from List if Already on the Customers List | Dueling Excel - Episode 1109
1,646 views · Published 25 September 2009 · 6:53 · Indexed 6 November 2026
Channel: MrExcel.com · 2009 · Education
Microsoft Excel Tutorial: Delete Prospects from List if Already on the Customers List | Dueling Excel. Welcome to another exciting episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head to head to find the best solution to your Excel problems. In this episode, we tackle a question sent in by one of our viewers via YouTube. They want to know how to delete prospects from a list if they are already on the customers list. To solve this problem, I use the MATCH function to compare the two lists and identify any duplicates. I also show you how to use the View, New Window, and Arrange All features to view both worksheets side by side. This allows you to easily compare the two lists and identify any duplicates. On the other hand, Mike uses the VLOOKUP function to achieve the same result. He also introduces the COUNTIF function as an alternative solution. However, he shows a clever trick to avoid the annoying sheet reference when using this function. We also discuss the limitations of our solutions, such as the possibility of missing duplicates due to spelling variations. We mention the concept of Fuzzy Match as a potential solution for this issue, but we leave it for another day. So, whether you prefer the MATCH or VLOOKUP function, or want to try out the COUNTIF trick, this episode has something for everyone. We hope you find our solutions helpful and we look forward to seeing you in our next Dueling Excel podcast. Don't forget to subscribe to our channels for more Excel tips and tricks. Thanks for watching! 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:25) Explanation of the question and method to be used (00:43) Setting up the spreadsheet for the solution (01:00) Creating a new column for the solution (01:10) Using the MATCH formula (01:23) Explanation of the MATCH formula (01:33) Using #N/A to indicate if the prospect is on the customer list (01:45) Explanation of the #N/A result (01:55) Using Data and A-to-Z to delete the records (02:11) Introduction by Mike (02:23) Using VLOOKUP as an alternative solution (03:10) Explanation of using VLOOKUP in this case (03:24) Sorting the results (03:39) Using COUNTIF as a third solution (04:05) Explanation of using COUNTIF (04:39) Using COUNTIF with two windows open (05:22) Sorting the results (06:23) 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: Countif function Customers worksheet Deleting records in Excel Excel formulas Excel tips Fuzzy match formula Match function MrExcel and Excel Is Fun podcasts Prospects worksheet Sorting data in Excel Vlookup function Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152642/ BackIn2Shape at YouTube has two worksheets; one of customers, and one of prospects. If a prospect is on the customer list, he would like to delete it. Episode 1109 show you how.
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