Home » Questions » Computers [ Ask a new question ]

How to filter an Excel column for entries matching a master list

How to filter an Excel column for entries matching a master list

The general question entails sorting a large Excel 2007 list to find entries that match smaller subset list.

Asked by: Guest | Views: 62
Total answers/comments: 1
Guest [Entry]

"I agree with the approach of adding alternative spellings to your master list (you might have a second column to tell you which one is your preffered format for mailing etc, and which is just to match the company data).
You might have some success using successive SUBSTITUTE functions to generate an alternate verion of the names. eg

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"" inc"",""""),""."",""""),"" "","""")...

So each substitution replaces any instance of the selected text with the replacement - nothing in our case here.
From my experience of similar fuzzy matching between names from disparate systems, you may have to drop things like inc, corp, plc etc to get matches. While you can use SUBSTITUTE for this, you could get some odd results with things like ""Income Corporation"" becoming ""omeorporation"", so it may be safer to use this sort of thing:

IF(RIGHT(lower(A1),4)=""corp"",left(lower(A1),len(A1)-4)),lower(A1)).

Do the substitute for spaces last.

You could use MATCH or COUNTIF with similar results to give a column showing which transactions match up to your list.

An alternative would be to use you master list as the criteria to base an advanced filter from, which would enable you to very easily take a copy of the transaction list entries which match your customer names, and place this filtered copy elsewhere (eg off to one side, or on another sheet). Just as with the above, you would still need to add variants where they are too distant from your original name."