Proofing a hotel list

[BHamilton50]

Greetings!

I'm running Windows 11 Pro version 22H2 and using Excel in Microsoft 365 Apps for Business version 2303 (Build 16227.20318 Click-to-Run).

From a certain company I receive an initial list of people's names in Excel who are scheduled to stay at a hotel.

I then receive a master list of people's names from the same company who will actually be staying there.

The master list can be different in a few ways; either the names are spelled differently than the names in the initial list, or the master list no longer has names that the initial list has due to cancellations.

I not only want to combine the 2 spreadsheets into one (one sheet on one tab and the other sheet on another tab) but I want to use, if possible, an IF/THEN formula to proof the master list against the initial list and give me the corrected list (the proof) on a 3rd tab.

The lists can sometimes contain hundreds of names, so proofing can take quite a long time.

Is this possible with the IF/THEN formula? If so, what is the equation that can be used?

[#2]

Hi @BHamilton50 

The master list can be different in a few ways...the names are spelled differently than the names in the initial list

So basically you're trying to compare/match Apples & Oranges. An IF/THEN won't be of any help for this kind of exercise (or I misunderstood what you actually expect), a Fuzzy Lookup can, to a certain extent...

I haven't used the FuzzyLookup add-in for Excel for years so not sure it installs/runs on 365. On the other hand Get & Transform aka Power Query offers that functionnality as well - see Create a fuzzy match (Power Query)

[#3]

@L z. 

Thank you for your response to this.

I'd say 'kinda' on the apples to oranges comparison. It's comparing 1 list of names to another list of the same names with possibly slightly different variations in some of the names.

Most of the names on both lists, however, do match up, so the differences are probably on a very small percentage of the names.

The information in the details section of the link you sent, though, sounds about what I am looking for.

I haven't tried your solution, but I plan to do that this week.

Thank you so much for what you sent! I'll send an update when I have one.

[#4]

Hi @BHamilton50 

Not sure what you looked at but I would recommend the Power Query route

Another recommendation (I had surprises that are difficult to explain...): if names in one or both list(s) contains non-alpha chars., ex.: comma, underscore... start by removing those chars before trying to Fuzzy match