Data comparison showing detailed output

[DJSTYLi]

Hi,

I have two sets of data with the same columns:

ID, Amount, Number, BillingCompany

I have these in two worksheets within one spreadsheet file, named W
and Sage respectively.

I want to establish differences between the two sets of data, an
establish:

what IDs appear in W2 but not Sage
what IDs appear in Sage but not W2
What IDs appear in both but have different values in the other column
(Amount, number, billingcompany etc.)
For this final criteria, I need to show the data of both records e.g.

ID 1 Amount 100 Number 23 BillingCompany 2
ID 1 Amount 100 Number 22 BillingCompany 2

etc.

but for the first two just the ID is fine.


Can anyone help me out? I have been struggling with this for a whil
now!

Thank

 

[Tom Ogilvy]

1 and 2 can be done with

=if(countif(OtherSheet!A:A,A1)=0,"Unique","Match")

and drag filling down the sheet. Then you can filter on this column and
copy your data to another sheet/location if you want.


You can get a side by side comparison by using vlookup, then put in one
additional formula that compares the appropriate columns and flags a
difference (or use conditional formatting).