Sheet Comparisons

[Jeff]

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.

 

[Otto Moehrbach]

Jeff
This macro will do that. Note that I assumed the sheet names are "One",
"Two", and "Three". I included some code to check if a SSN in sheet "Two"
is not in sheet "One". I also assumed that your data is in Columns A & B.
Also, this code will work only if each SSN in each of the first 2 sheets
appears only once, if at all, in each sheet. HTH Otto
Sub GetUnique()
Dim ColAOne As Range
Dim ColATwo As Range
Dim i As Range
Dim Dest As Range
Sheets("One").Select
Set ColAOne = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set ColATwo = Sheets("Two").Range("A2", .Range("A" &
Rows.Count).End(xlUp))
Set Dest = Sheets("Three").Range("A" & Rows.Count).End(xlUp).Offset(1)
For Each i In ColAOne
If ColATwo.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then
i.Resize(, 2).Copy Dest
Set Dest = Dest.Offset(1)
Else
If ColATwo.Find(What:=i.Value, Lookat:=xlWhole).Offset(,
1).Value <> i.Offset(, 1).Value Then
i.Resize(, 2).Copy Dest
Set Dest = Dest.Offset(1)
End If
End If
Next i
For Each i In ColATwo
If ColAOne.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then
i.Resize(, 2).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

 

[Pete_UK]

You could obtain a unique list of the SSNs by copying from the first
sheet into column A of the third sheet, and then by copying from
Sheet2 to the bottom of the list in Sheet3, and then applying Advanced
Filter - Debra Dalgleish shows how here:

http://www.contextures.com/xladvfilter01.html

under the section "Filter Unique Records".

Then in Sheet3 you could have a formula like this in B2:

=IF(ISNA(VLOOKUP(A2,Sheet1!A:B,2,0)),0,VLOOKUP(A2,Sheet1!A:B,2,0))
+IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,0)),0,VLOOKUP(A2,Sheet2!A:B,2,0))

assuming that one SSN can only appear once in either Sheet1 or Sheet2.

If there might be duplicate SSNs in Sheet1 or Sheet2, then you could
try this:

=SUMIF(Sheet1!A:A,B2,Sheet1!B:B)+SUMIF(Sheet2!A:A,B2,Sheet2!B:B)

Then copy the formula down column B of Sheet3.

Hope this helps.

Pete

 

[Max]

You've a response in your multi-post in .worksheet.functions, too
Pl don't multi-post