Compare two worksheets

  • Thread starter Thread starter AJS
  • Start date Start date
A

AJS

What I want to do is compare two worksheets that I import
data too and on another worksheet have where there are
differences in any part of the rows.

What I mean by this is that I want to compare a new set of
data by looking up a product code to an old set of data and
show entire rows where any of the data in the row has
changed. If nothing has changed on the row then nothing
shows up.

Complicated?

I think VLOOKUP and a few IF statements might helpe but I
can't quite see how I can logically put it together.


Here's a cut back example of what I might have.
OldData
CODE STOCK PRICE
1234 300 $30
4352 44 $145
4341 19 $303
9087 29 $55

NewData
CODE STOCK PRICE
1234 300 $30
4341 18 $303
9087 29 $50
4909 200 $44

The New WorkBook should show something like...

ChangedData
CODE STOCK PRICE

4341 18 $303
9087 29 $50
4909 200 $44

Possibly with something for items no longer active
but that is a bit easier to do with VLOOKUP comparing
product codes from the old to the new data.
 
Try this set-up:

Set-up assumes your sample data as posted
is in cols A to C in sheets named OldData and NewData
with the desired results to be in sheet: ChangedData
(Row1 is col headers, data from row2 down)

In OldData
--------------
Put in D2: =TRIM(A2&"_"&B2&"_"&C2)
Copy D2 down

In NewData
--------------
Put in D2: =TRIM(A2&"_"&B2&"_"&C2)
Copy D2 down

Put in E2: =IF(ISNA(MATCH(D2,OldData!D:D,0)),ROW(),"")
Copy E2 down

In ChangedData
-------------------
Put in A2:

=OFFSET(NewData!$A$1,MATCH(SMALL(NewData!$E:$E,ROW()-1),NewData!$E:$E,0)-1,C
OLUMN()-1)

Copy A2 across to C2, then copy down until #NUM appears

Format col C as currency

Cols A to C will return what is wanted - i.e.
only the rows in NewData where there is changed data
compared with corresponding rows in OldData
 
Back
Top