Comparing Large Amounts of Data between Excel Spreadsheets RRS feed

  • Question

  • I have 2 spreadsheets, 1 (the "master") which contains 62890 lines of data and another spreadsheet which contains 8050 lines of data.  I need to compare the smaller list to the "master" list.  I have MS Excel 2007.  Can anyone walk me through the steps to create a query to do this comparison? 
    Friday, July 20, 2012 2:09 PM

All replies

  • Do you have a key value column, or is the data unique only when taken as an entire row?

    HTH, Bernie

    Friday, July 20, 2012 5:14 PM
  • Data is unique as a row.

    Karen Meyer

    Friday, July 20, 2012 6:27 PM
  • For each list, create a key value that will be unique - just concatenating the values together will often work. To do this, insert a new column A and B in each sheet, and use a formula like this in column A:


    where Z is the last filled column and the ..... means to include all columns. Then copy down to match your data. Convert the formulas to values, and then in Cell B2, use a formula like


    and copy down. Any formulas that return errors indicate that the values on that row don't completely and identically match any values in the other table, and if the formula returns a number, it will be the row number in the other table.

    If you need to check for duplicates within a table, use a COUNTIF formula to check the count of values in column A being greater than 1.

    HTH, Bernie

    Friday, July 20, 2012 6:43 PM
  • Thanks, Bernie.  I have been out of the office on vacation for a few days and came back to your response.  I must admit you are speaking outside of my understanding....  I am functional but not "fluent" on Excel as you.... 

    The columns I need to compare within "table 1" are C, D, and H ~ with their counterparts in "table 2," columns F, G, and J.  (The columns on each table show lastname, firstname, and state.)  There are other columns in each table, but they do not need to be compared.

    To create/insert the function/formulas for this task, I create a new column A and a column B; beyond that, I am formulaically challenged.  Might you break it down even further - perhaps pen the 'script' for each formula?


    Karen Meyer

    • Edited by kwhite129 Monday, August 6, 2012 5:31 PM
    Monday, July 30, 2012 8:27 PM
  • Hi, Janet.  Bernie's explanation was WAY above my understanding.  You will see I responded with more questions/a sense of confusion.  Hopefully, it can be broken down further.  Thanks.

    Karen Meyer

    Monday, July 30, 2012 8:32 PM