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

Watch on YouTube

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