I get very large excel files (i.e., over 500K records) with a list of ID's from vendor1 in column A and a list of ID's from vendor2 in column B. Sometimes these values are different between the vendors, but they are usually very similar.
I would like to find the value in column A that is the closest match in column B. The matched value can go in column C.
In other words what is the fastest formula/VBA that can find a value in column A with all the same characters. (ie., E21R = R2E1 = 2 1RE etc.,) If it can't do that, it finds the value in column A with the most similar characters. (ie., E21R = E21 etc.,)
Column A Column B Column C (Closest Match)
E22,2 4TR T,GRTYE2 TRE GTY 2,
TRE GTY 2, E4TR22 E22,2 4TR
Notice in row 1 that column B&C have the exact same characters just in different order.
Notice in row 2 that there was not an exact match, but column C contains the value with the most similar characters.