ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Data comparison showing detailed output (https://www.excelbanter.com/excel-programming/316823-data-comparison-showing-detailed-output.html)

DJSTYLi

Data comparison showing detailed output
 

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

--
DJSTYL
-----------------------------------------------------------------------
DJSTYLi's Profile: http://www.excelforum.com/member.php...fo&userid=1511
View this thread: http://www.excelforum.com/showthread.php?threadid=27829


Tom Ogilvy

Data comparison showing detailed output
 
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).

--
Regards,
Tom Ogilvy


"DJSTYLi" wrote in message
...

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 W2
and Sage respectively.

I want to establish differences between the two sets of data, and
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 columns
(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 while
now!

Thanks


--
DJSTYLi
------------------------------------------------------------------------
DJSTYLi's Profile:

http://www.excelforum.com/member.php...o&userid=15119
View this thread: http://www.excelforum.com/showthread...hreadid=278292





All times are GMT +1. The time now is 03:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com