Share via

I need to compare two tables and return the differences.

Anonymous
2012-05-18T16:33:28+00:00

I have two largish tables, one with previous week's data ('previous') and one with current week's data ('current'). I want to be able to compare the two tables and return whatever data has changed from the new table, as well as the key field in the row. This is complicated by the possible insertion of rows in the middle of the new table. So an item that is in row 25 on the 'previous' table is now on row 26 on the 'current' table.

What is the best way to do this?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2012-05-22T08:22:29+00:00

    Yes it will note the changes and return False for the two rows C 30 and E 80.

    Having marked all changed rows with FALSE, you can either SORT Them, or FILTER them to get the list of FALSE entries together. Or you can use conditional formatting to highlight the changed rows in different Colour.

    Let me explain the formula in detail to clarify the concept.

    =IFERROR(VLOOKUP($A2,Sheet1!$A$2:$C$5,2,FALSE)=B2,FALSE)

    Let us first look at the inner part of the formula which is:

    VLOOKUP($A2,Sheet1!$A$2:$C$5,2,FALSE)

    What VLOOKUP does in effect is that, it searches for the Key in $A2 of Sheet 2 in the first column of the Table in Sheet 1 ($A$2:$C$5) and if it finds a matching key there it returns the data in 2nd column of the Table (i.e. the sales column), The FALSE stands for an "EXACT MATCH" (instead of a TRUE which stands for nearest match).

    So the expected returned values of this VLOOKUP function will be as follows:

    Key Sales Compare

    A    10                10

    B    20                20

    C    30              #N/A

    D    40                40

    E    80                 50

    Note that since the system is unable to find "C" in the First Sheet, it returns a #N/A error. Effectively all new rows will be marked with #N/A errors.

    Now the task boils down to compare the figures in column B of Sheet 2 which is the Current Sales vs. the Previous Sales which have come using the VLOOKUP formula.

    So, if we just use the equation:

    =VLOOKUP($A2,Sheet1!$A$2:$C$5,2,FALSE)=B2

    it returns TRUE for all cases, where the sales remain the same.

    it returns FALSE for all cases where the sales column varies.

    it returns #N/A for all cases where the comparison is done for an #N/A field

    Key Sales Compare

    A    10                TRUE

    B    20                TRUE

    C    30               #N/A

    D    40                TRUE

    E    80               FALSE

    Finally to remove the error, we forcibly change all errors to FALSE by using the IFERROR function.

    Hence the final Formula.

    =IFERROR(VLOOKUP($A2,Sheet1!$A$2:$C$5,2,FALSE)=B2,FALSE)

    results in :

    Key Sales Compare

    A    10                TRUE

    B    20                TRUE

    C    30              FALSE

    D    40                TRUE

    E    80               FALSE

    Please note that VLOOKUP does not match ROW-BY-ROW, it searches the KEY in the next sheet from TOP to BOTTOM (hence the V for VERTICAL in VLOOKUP) till it finds a Match.

    Hope it is clear.

    3 people found this answer helpful.
    0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-05-19T07:55:22+00:00

    You can use the VLOOKUP function which can search for the key field on your this weeks data sheet with the key field in the previous field and compare the matching data.

    Sheet1 - Last Week Data (Starting from A1)

    ===================

    Key       Sales        

    A              10             

    B              20            

    D             40             

    E              50              

    Sheet2 -Current Week Data (Starting from A1)

    =====================

    Key       Sales     Compare    

    A              10               

    B              20              

    C             30                

    E              80                

    In cell C2 of Second Sheet you can write the following formula:

    =IFERROR(VLOOKUP($A2,Sheet1!$A$2:$C$5,2,FALSE)=B2,FALSE)

    Then copy this across to C3:C5

    All "False" would mean change in the sales figure for that key or addition of a new key.

    You can also use Conditional Formatting to highlight non-matching records using the above formula.

    The concept can be extended to multiple columns also by appropriately changing the third parameter of VLOOKUP.

    Finally, please note that this would not highlight any keys which were in the previous week and have been deleted from the current week. For that separate VLOOKUP formula will need to be written in Sheet1.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2012-05-21T13:28:46+00:00

    Hi,

    are there, new entries in the 'current' sheet

    and you want to transfer in the 'previous' sheet?

    or you have only, some (all) data that have changed?

     

     

    Yes, there could be new entries, probably in the middle of the spreadsheet. So what was row 345 on the previous week, could be row 346 or 347 on the current week.

    0 comments
  3. Anonymous
    2012-05-19T12:44:49+00:00

    Hi,

    are there, new entries in the 'current' sheet

    and you want to transfer in the 'previous' sheet?

    or you have only, some (all) data that have changed?

    0 comments
  4. Anonymous
    2012-05-19T06:13:15+00:00

    Refer the following Office online article to compare two tables and find records without matches:

    http://office.microsoft.com/en-us/access-help/compare-two-tables-and-find-records-without-matches-HA010205132.aspx

    0 comments