+ Reply to Thread
Results 1 to 10 of 10

Monster name list compare

  1. #1
    Registered User
    Join Date
    07-03-2007
    Posts
    6

    Monster name list compare

    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.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    07-03-2007
    Posts
    6

    Thanks

    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.
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try zipping the Excel files with WinZip or similar software and repost here....the txt files are too messy.

  5. #5
    Registered User
    Join Date
    07-03-2007
    Posts
    6

    Sorry about that

    Okay here goes
    Attached Files Attached Files

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.

  7. #7
    Registered User
    Join Date
    07-03-2007
    Posts
    6

    Yeah I'm having that problem myself

    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.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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#.

  9. #9
    Registered User
    Join Date
    07-03-2007
    Posts
    6

    Thanks

    Hey that worked pretty well, I also simplified my lists immensely but your function delivered good results. Thanks for helping me out.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Great! No problem!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1