Excel help - how to find the differences between two similar data sheets

Carol369 

This is a difficult task because any time partial matching is involved one cannot guarantee 100% accuracy.

This 365 solution takes a given text and goes through a progression of checking a character accumulation string against list 1. For example, with Howard's Plate Inc. it checks:

H

Ho

How

Howa

Howar

Howard

Howard'

Howard's

Howard's P

Howard's Pl

Howard's Pla

Howard's Plat

Howard's Plate

Howard's Plate I

Howard's Plate In

Howard's Plate Inc

Howard's Plate Inc.

Naturally, with using partial matching it will easily find only a few letters in list 1.  Max is added to select the highest number match from the list to ensure optimal accuracy.

The formula includes an accuracy check. You may specify how much of the string to match.

=LAMBDA(input,acc,LET(strings,MID(input,SEQUENCE(LEN(input)),1),s,SCAN("",strings,LAMBDA(a,v,a&v)),m,MAX(IFERROR(XMATCH(s&"*",List1,2),0)),IF(m>=acc*LEN(input),m,0)))

The above Lambda is then folded into a smaller formula:

The .4 indicates how much of the string will be checked against list 1. You can adjust this as needed to get more results.

=BYROW(list2,LAMBDA(row,IDrow(row,0.4)))

I'm familiar with partial matching techniques using LEFT, RIGHT, MID and wild cards.  I've seen data entered differently in two lists and I know it can be a time sink. Hopefully, this formula can be put to use.