Attention: Please take a moment to consider our terms and conditions before posting.

Help Needed - Database List Comparison

edited March 2012 in Not Sports Related
I am sure the title of this thread won't bring too many people to the discussion - but I need some help from any database-savvy people out there! I don't have much time to work this out...

I have a list. In that list is 950 entries. I have another list, which has about 850 entries. I need to cross-reference the two lists so that I am left with the 100 oddities that my team can then update and fix. As I am not very clever with Excel, and the Google results I am finding aren't cutting it, I'm hoping someone here can help me figure out the (presumably very simple) way this ought to be done!

Boy do I hope this isn't the last post on the thread. Help!


  • Options
    Assuming you have a common field in the both sets of data, use the vlookup function in excel to look up the common field in one set of data by referencing the key field in the other. Any items that aren't looked up should have #NA next to them, these are the ones you are looking for. Have a look at the help for the Vlookup function its (relatively!) simple

  • Options
    I can write you an Excel macro easily enough that looks for each of the 950 in the list of 850 and tells you which ones are missing. However, this would be looking for perfect matches. Software that detects similar entries is far more effort. Inbox me but bear in mind I am +7 hours to you.
  • Options
    I'd use countif over vlookup, that way you know how many times an entry appears in the other list. Could also have a second countif column to count the number of entries in the same list, then compare the two numbers.
  • Options
    edited March 2012
    Aye, you need something common in both lists, if not see if you can make one. Names, Address, DateofBirths, whatever can make each entry unique.

    Then in the one you want to see if it matches. Do...


    Key1 being your unique key in one of the lists
    LookupList being your second list
    1 will simply return the first column
    False will look for an exact match

    When doing the lookup the unique key must be in the first column of your lookup list. ie Everything that could be in B1, B2, B3 etc needs to be in column D.


    I find it easier highlighting the whole of the column to save putting $ signs in to lock the cells when you copy the formula all the way down.

    p.s Excel is for maths, not for databases ;)
  • Options
    Brilliant - thanks guys. The list is of movie titles, so it's just one field. I'll try some of these ideas and hopefully come up with the goods. Cheers!
Sign In or Register to comment.

Roland Out Forever!