Partial Match for Group Names

Hi,

I've a list of group names in a column in a sheet in Excel. I would need some help to code formulas to identifying the nearest / partial match for these groups.

Currently, I'm doing vlookup to identify exact group name matches and manually identifying partial matches.
Once all matching is done, then I use EXACT() function to compare the matching group name obtained using vlookup.

Can anyone help in matching the strings (identifying partial matches as well) ?
Refer attached sample worksheet with few examples.

Let me know if you need any information. Any help would be greatly appreciated.

Sarang

C2=IF(OR(EXACT(A2,B2),SUMPRODUCT(COUNTIF(A2,"*"&TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),(ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1))-1)*LEN(B2)+1,LEN(B2)))&"*"))),B2,"")

D2=IF(EXACT(A2,B2),"Exact Match",IF(SUMPRODUCT(COUNTIF(A2,"*"&TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),(ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1))-1)*LEN(B2)+1,LEN(B2)))&"*")),"Partial Match","Not Match"))
TRY THIS AND COPY TOWARDS DOWN

Thanks a lot Siva. The formulas work beautifully.

However, I would like a more dynamic way - kind of lookup / match between group names in col A and B.

The group names in col A in the worksheet need not exactly match with the corresponding cell in the adjacent col B. So, need to match / lookup the col B for a particular group name in col A and fetch the matching group name from col B.

In other words, the matching group name in col B may be available anywhere in that column for a given group name from col A.

How can such a formula be coded ?


Sarang

Hi,
Will INDEX, MATCH and Lookup functions be of any help ? How can it be coded in case of Partial matching group names ?
Does anyone have any ideas ?

Sarang

Hi,

There are two master group name lists available from where the group names will be matched.

I tried coding a formula as suggested in the link shared by azumi. The SUMPRODUCT function in C2 column suggested by Siva will work exactly in an array formula as I envisage, but I stumble while trying to code it.

Attached an updated sample workbook with examples, as shown in Sheet1 in the attached file.

Can anyone help code an array-formula accordingly ?


Sarang

Originally Posted by azumi

Not sure for the results, I give you 2 formula, hope the results can meet the expectation...

Cheers

Hi Azumi,

The 2nd formula doesn't work, can you explain how the 1st formula works ?

I tried using your formula in Sheet1, it either returns only the first value from col B when matched with group names in col A or returns blank value (IFERROR returns true).


Sarang

Hi,

In Sheet1, I want the matching rows from col B for a given group name from col A.

I tried using an IF condition where I coded a VLOOKUP on group names from A with the groups in col B. If an exact match is found, then the matching group name is returned from col B, if there is partial match or no match, then #N/A is returned. There are multiple possibilites for a partial match.

Examples of Partial matches are as follows :

For e.g., as shown in Sheet1, when
1. Auto IT Management Report Runners is searched and Auto IT Management Report Runner or vice versa is found, or
2. Back Office Reporting_NPI Innov Report Runner is searched and Back Office Reporting_NPI Report Runner is found or
3. Auto Mobile Sales Report Viewer is searched and FS Auto Mobile Sales Report Viewer is found or
4. Fraud Innovation NPI Restricted Report Runner is searched and Fraud Innovation Restricted NPI Report Runner is found or
5. Collections Innov Report Viewer is searched and Collections Innov Report Runner is found

If all the above 5 scenarios fail, then it can be concluded that the group name being searched does not exist in col B.

I tried to build an array (to compare the group name) as how Siva has used it in his formula in the criteria part of the COUNTIF inside the SUMPRODUCT function, so as to use it with a MATCH function to find the row # of the group name from col B, but I'm struck and getting an error. If this works and the row number can be found, then the INDEX function can be used to fetch the group name from col B.

Can anybody help me coding array-formulas that would resolve the above scenarios in case of a partial match ??

Or

Check Fuzzy Lookup

http://www.microsoft.com/en-in/downl....aspx?id=15011

Originally Posted by azumi

I didnt' get it why you apply the formula on Sheet1 ? You have different lay out with blanks on Group Name and multiple results for each group name.

I've an master list of group names.. I believe fuzzylookup add-in suggested by Sixthsense will work.. Will check it out and post further updates..

The VBA code available (in post #2) in the below link worked like a charm.

Fuzzy Matching - New Version

I did not use the Fuzzy Lookup add-in. My requirement is resolved now.