Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Detailed Pivot Results when some row data is identical | Excel Discussion (Misc queries) | |||
Use detailed data in one worksheet to create summary data as chart source | Charts and Charting in Excel | |||
Display detailed data in a pop up box | New Users to Excel | |||
Showing data per month & year for comparison | Excel Discussion (Misc queries) | |||
Getting output from an excel output back to cscript.. | Excel Programming |