Need Function that can identify spelling mistakes in one list compared to another list

[Alec_Solomons]

Hi guys, newbie here.

I am hoping to find a function/formula that can help me with identifying spelling mistake with names (scientific fish names in my case) in one column by comparing it with certified/correct names that are in a seperate column and then replacing the incorrect names with the correct names.

Basically, looking for a more powerful version of "Find and Replace" that can crunch through large data-sets.

As an example, I have attached a file which has 2 columns. Column A "Names for checking" being the list with mistakes in them... And Column C "Fish List" being the one with correctly spelt fish names.

Cheers
Alec

[AskMeAboutExcel]

The easiest solution for this is to use 'fuzzy matching' logic with Power Query in Excel.
Takes a little homework to understand if you are new to Power Query, but its very quick/powerful. A guide here: https://support.microsoft.com/en-us/...4-bd3962b90649

I've mocked up an example of this in the attached, by taking the following steps
1) Created a connection called 'CheckNames' from your Column A
2) Created a connection called 'ProperNames' from your Column C
3) Merged the two queries with the following parameters
- Left table = CheckNames
- Right table = ProperNames
- Fuzzy match, Left Outer Join (every item on 'left' table has a row), Number of matches = 1 (one response per row), Threshold = 0 (show best guesses, even if they are terrible)

Hope that gets a result similar to what you are looking for. You can play around with the 'Threshold' parameter until you get sensible looking outcomes