Monster name list compare

**NoahB** · 07-03-2007, 09:25 AM

I've tried a few different tactics to compare two enormous lists of names, but I cannot get it to work to my satisfaction. I am attempting to get them to follow approximately the same formats, but because they are from unique sources they have very different information leaving me with no choice but to actually compare by name, rather than customer number or something useful like that. Given the variable nature of names (in this case company/corporation names) I've had a difficult time getting the lists to match in a way that provides meaningful results, but one of my lists is 9,000 strong so I need to figure out a way to automate this task. If anyone has any useful suggestions I would really appreciate it.

**NBVC** · 07-03-2007, 09:41 AM

Have you looked at the Match() function?

e.g. =Match(A1,Sheet2!A:A,0) will return a number if a match is found comparing A1 to Sheet2 column A. It will return N/A# if no match found.

If this doesn't work for you, you will need to give some relevant example of what you are trying to match to what.

**NoahB** · 07-03-2007, 10:36 AM

Thanks for the quick response. I gave your method a shot, and I think it would work, however the format that I have received these lists is making this more difficult. I have attached an example that I hope will useful. I have tried breaking down the list with the drop down menus onto separate sheets and using the MATCH function, but I still found no matches. I think I need to combine the 'Bill to' and 'Ship to' lists, or use a function that can find partial matches. I should probably also try to standardize the way the names are displayed, for example Bank of New York could be BK, though on one list it continues to have a second, more explicit name such as Bank of New York/ Countrywide. In any case, I have attached an example (though for some reason it will not allow me to attach it as an .xls file) and if there are any suggestions on ways to make this work it would again be greatly appreciated.

**NBVC** · 07-03-2007, 12:04 PM

Try zipping the Excel files with WinZip or similar software and repost here....the txt files are too messy.

**NoahB** · 07-03-2007, 12:36 PM

Okay here goes

**NBVC** · 07-03-2007, 12:53 PM

Can you re-iterate what exactly you want to match up? I don't quite see the connection.

Give some examples of what you consider might consider matches.

**NoahB** · 07-03-2007, 01:06 PM

The idea is that on sheet 1 there is a list of companies under the headings bill and send, and on the second sheet there is a second list of companies. My assignment is to figure out which ones match up. For example Lehman Brothers on sheet 1 matches up to Lehman Brothers on sheet 2, except that on sheet 2 it has more specific information, which is giving me a headache. I need to determine which companies are on both and which are unique.

**NBVC** · 07-03-2007, 01:47 PM

Not sure how much this helps...but I did get some results...

Try In Sheet 1, E2: =MATCH("*"&D2&"*",Sheet2!A:A,0) copied down

if any of the strings in Column A of Sheet2 contain the text in column D of sheet 1, then you will get a number returned (which represents the position in Column A that the match was found), else, you get a N/A#.