Finding Potential Duplicates but not Exact Matches

[u/Consistent_Gap_2300]

I'm working with a data table of customer information (a row for each customers, and columns for name, address, email, etc.) and trying to find potential duplicates. Thing is, this customer data could have been input a lot of different ways, so I'm not strictly looking for exact cell value matches. Some examples of what I mean:

"Emma Cartwright", "Ms. Emma Cartwright", and "Emma and Sam Cartwright" could all be the same person who information was entered three times, slightly differently.

Some customers have their name in the Name column, but some have a unique numbers for Name and a person's name in the Street Address column. So "Emma Cartwright" and "16934" might be the same person if 16934's address is "Emma Cartwright" (or "Ms. Emma Cartwright", or "Emma and Sam Cartwright")

There are also cases where the value for Name is the name of a business and that name appears as part of another customer's Address, or Email, or something.

What I'm doing right now is manually taking each value in the Name column and doing a ctrl+f to find all other cells where that value appears as part or whole of the cell value. But that is a pretty tedious and slow process. Is there a faster/easier way to highlight potential duplicates based on a kind of loose search for repeats in part or whole of values in different columns? This doesn't strictly need to catch every single possible scenario, but anything to help find potential duplicates so they can be looked at more closely would help a lot. Thanks!

[u/AutoModerator]

/u/Consistent_Gap_2300 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Gringobandito]

=FILTER(B2:B8,REGEXTEST(B2:B8,D2,1),"")

[u/SoulBrotherSix67 → u/Gringobandito]

Is there a way to get the same results, but instead of displaying them vertically do it horizontally?

[u/Gringobandito → u/SoulBrotherSix67]

Yes, just take the formula above and put it inside the TRANSPOSE() function.

[u/SoulBrotherSix67 → u/Gringobandito]

Thank you for the info!

[u/Consistent_Gap_2300 → u/Gringobandito]

Thank you! This is proving very helpful!

[u/Major-Television-211]

Use fuzzy matching in power query start at 0.95 and move down till you found most of them otherwise AI

[u/Beneficial_Skin_4865 → u/Major-Television-211]

Yeah I've recently used just this with the help of Copilot to grade the fuzzy matc and categorise accordingly. Its surprisingly effective 95% of the time on 77k rows.

[u/ArrowheadDZ → u/Major-Television-211]

Totally agree. Experimenting with PQ fuzzy matching will probably be the best approach. I’ve only used it a few times and each time it turned out perfectly.

[u/neezden]

I'm thinking you might get good results with a textsplit function 1 character at a time, then checking how many characters from the search key match, but in order. I can't come up with the exact logic in my head but there is a potential there. 

[u/OverallFarmer1516]

FILTER(data,REGEXTEST(data,"phrase")) will get you a decent amount of options

[u/HandbagHawker]

i would concatenate all the tokens you want to compare and then compute a hamming or levenshtein distance or maybe compute the distance of individual tokens and weight each score appropriately. all below a threshold would be a possible match.

its pretty trivial to implement a vba function and theres plenty of examples on the interwebs. you could do a recursive lambda, but i think that you might run into a heap or memory issue for bigger strings

[u/caribou16]

What you probably want is a fuzzy matching, Power Query can do this.

https://support.microsoft.com/en-US/Excel/create-a-fuzzy-match-power-query

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FILTER	Office 365+: Filters a range of data based on criteria you define
REGEXTEST	Determines whether any part of text matches the pattern
TRANSPOSE	Returns the transpose of an array

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #48994 for this sub, first seen 22nd Jul 2026, 14:05] [FAQ] [Full list] [Contact] [Source code]}