Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Comparisons.
I have a 3 sheet file. Sheets 1 and 2 are exactly the same. There is an
SSN and an amount in each sheet. something similar to the following. Ssn 123 Ssn 222 Ssn 222 Ssn 555 Ssn 333 Ssn 444 Ssn 666 I need sheet 3 to contain the SSNs and the amounts of the values where sheet 1 and sheet 2 are different. So if sheet 1 had an ssn of 123-45-6666 and an amount of 1.23, and sheet two had the same ssn and an amount of 3.43, i would need this ssn and amount populated in sheet 3. Any suggestions??? Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Comparisons.
Here's an approach to frame up the comparisons ..
Assume SSNs running in A2 down, amounts in B2 down in both Sheet1/2 To compare Sheet1 with Sheet2 In Sheet1, Put in C2: =IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"SSN_not_in_Sheet 2", IF(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))=B2,"OK ","Amt_doesn't_match")) Copy C2 down to the last row of SSN data in col A. Then use autofilter on col C to retrieve unmatched cases by category for further action Similarly .. To compare Sheet2 with Sheet1 In Sheet2, Put in C2: =IF(ISNA(MATCH(A2,Sheet1!A:A,0)),"SSN_not_in_Sheet 1", IF(INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0))=B2,"OK ","Amt_doesn't_match")) Copy C2 down to the last row of SSN data in col A Use autofilter on col C to retrieve unmatched cases by category for further action -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jeff" wrote in message news:mjmuj.5151$kD3.545@trnddc08... I have a 3 sheet file. Sheets 1 and 2 are exactly the same. There is an SSN and an amount in each sheet. something similar to the following. Ssn 123 Ssn 222 Ssn 222 Ssn 555 Ssn 333 Ssn 444 Ssn 666 I need sheet 3 to contain the SSNs and the amounts of the values where sheet 1 and sheet 2 are different. So if sheet 1 had an ssn of 123-45-6666 and an amount of 1.23, and sheet two had the same ssn and an amount of 3.43, i would need this ssn and amount populated in sheet 3. Any suggestions??? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Yr to Yr comparisons | Excel Discussion (Misc queries) | |||
Multiply IF AND OR comparisons | Excel Worksheet Functions | |||
Pivot Chart/Sheet comparisons -- adding or subtracting sheets | Excel Discussion (Misc queries) | |||
Spreadsheet Comparisons | Excel Discussion (Misc queries) | |||
Payroll sheet comparisons | Excel Discussion (Misc queries) |