Returning best matches after comparing data from 2 different sheets

**Dolke** · 03-22-2013, 09:10 AM

Hi to all,

I have need for a specific function and I don't know if such exists already in Excel, so I'm asking you guys to help me out.

I'm using Excel 2010.
In one file I have 2 sheets.
In one sheets column A i have some data. Similar data but no completely the same are in the sheets 2 A column. Data is alphanumerical, those are names of specific spare parts with some model numbers, letters and other characters.

Is there such function that can compare data from these sheets and return best matches into third sheet, or like in the example I made in the attachment, to return the result into sheets 2 column B?
As I said data is not completely the same, but if I have lets say this: GR VM 2000W HTR005GO 587565 in sheet 1, and I have this: GR.VM.1950w ARDO/ARISTON/WHIRLPOOL KOSI ř8.1mm L195mm AR5118 HTR001UN in the other to return this as a best match or to return some other if it is better matched then this one.

Please see the attached example:
example.xlsx

Ty all in advance.
Dean

**BB1972** · 03-24-2013, 12:37 PM

Hi Dean,

This probably isn't the answer you want, but I imagine you're flat out of luck here. Excel can compare like with like, can look for words (sub-strings) within strings, and with numerical data can return equal, next highest, and next lowest, but as for deciding what the best match is? I'm looking at your data now, and I have no idea why GR.VM.1950w ARDO/ARISTON/WHIRLPOOL KOSI ř8.1mm L195mm AR5118 HTR001UN might be the best match for GR VM 2000W HTR005GO 587565. So unless you can outline your logic for that being the best match, in a way that Excel can follow, I think you're unfortunately out of luck.

**Dolke** · 03-26-2013, 03:33 AM

Ty Brendan for the efforts.

I was afraid this kind of search and match procedure will require some sort of intelligent algorithm so, as that is not available, I would need to set up some parameter/logic for matching...
That is not simple task as well as those strings are representing 2 different way of labeling one same spare part. So one manufacturer put the brend name which is same in the both strings, and then they use all kinds of words and numbers of their own. So one producer will have code like: HTR005GO 587565 and other one AR5118 HTR001UN and that is basically the same spare item just labeled differently as in this industry (spare parts for home appliances) there are so many standards so all manufacturers want to set their own as major standard.

What I need is to somehow, get this 2 to get close to each other if you know what I mean... so I can connect data from one sheet/manufacturer to the data from second sheet/manufacturer in the best way possible that Excel can handle.

Any suggestions how to put this logic together maybe?

ty in advance
Dean