Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-22-2017, 01:02 AM
dylansmith dylansmith is offline How to compare 2 Excel sheets for 100+ matches? Windows 8 How to compare 2 Excel sheets for 100+ matches? Office 2013
Optimizer
How to compare 2 Excel sheets for 100+ matches?
 
Join Date: Nov 2011
Posts: 144
dylansmith is on a distinguished road
Default How to compare 2 Excel sheets for 100+ matches?

I have a master list of 3,000 names, and a second sheet that contains 150 names.

I would like to:

1. Highlight in 2nd sheet the names which can be found on the 1st sheet


2. Do a "contains" search rather than exact search on the name to get a hit.
For example, it should ignore spaces, recognize abbreviations (e.g. ltd = limited), or ignore the end of the name (private limited, or whatever I define to be irrelevant).


How do I go about doing this? If there is no ready-made solution, I can at least settle for a direct+exact match first (figured it'd be less complicated than what I need).

Thanks in advance for your help!
Reply With Quote
  #2  
Old 05-22-2017, 01:27 AM
xor xor is offline How to compare 2 Excel sheets for 100+ matches? Windows 10 How to compare 2 Excel sheets for 100+ matches? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Could you possibly upload your file and give some examples of expected results?
Reply With Quote
  #3  
Old 05-22-2017, 01:57 AM
dylansmith dylansmith is offline How to compare 2 Excel sheets for 100+ matches? Windows 8 How to compare 2 Excel sheets for 100+ matches? Office 2013
Optimizer
How to compare 2 Excel sheets for 100+ matches?
 
Join Date: Nov 2011
Posts: 144
dylansmith is on a distinguished road
Default

Sample worksheet here:

https://www.dropbox.com/s/18xclhekrd...mple.xlsx?dl=1
Reply With Quote
  #4  
Old 05-22-2017, 04:37 AM
xor xor is offline How to compare 2 Excel sheets for 100+ matches? Windows 10 How to compare 2 Excel sheets for 100+ matches? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

I regret, but I have no idea.
Reply With Quote
  #5  
Old 05-22-2017, 04:20 PM
Guessed's Avatar
Guessed Guessed is offline How to compare 2 Excel sheets for 100+ matches? Windows 10 How to compare 2 Excel sheets for 100+ matches? Office 2013
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

I can't help you with a fuzzy search but if there were exact matches then you could apply a Conditional Formatting rule to the first sheet
Code:
=COUNTIF('SEARCH LIST 4 NAMES'!$A$1:$A$7,$A1)
I can't see how a fuzzy search could possibly work. After all, the "Judean People's Front" is very different from the "People's Front of Judea"
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 05-22-2017, 09:09 PM
xor xor is offline How to compare 2 Excel sheets for 100+ matches? Windows 10 How to compare 2 Excel sheets for 100+ matches? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

I haven't tried it, but you might want to take a look at:

https://www.microsoft.com/en-us/down....aspx?id=15011
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to compare 2 Excel sheets for 100+ matches? How to find exact matches only in an excel spreadsheet zhead Excel 6 09-09-2016 07:52 AM
Compare filenames listed in Excel to external folder. Chayes Excel Programming 3 04-02-2016 02:17 PM
How to compare 2 Excel sheets for 100+ matches? Need to improve speed of Excel macro that searches for whole word matches Marrick13 Excel Programming 20 02-19-2016 09:54 AM
compare, match and count cell contents between sheets bobsone1 Excel 11 08-07-2014 10:34 PM
How to compare 2 Excel sheets for 100+ matches? Compare data in different columns in an Excel sheet kgfendi Excel 5 05-16-2009 05:42 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:08 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft