Non-Matching Entries
I wanted to do an automatic reconciliation via excel. It is simply that i want to compare two columns with each other and extract non-matching entries within them. Meaning that for each record in one column, only one record in another column must match. An example will more clarify this case:
This is the result i want, for each record in column B only one record in column A must match and extracting only the non-matching entries in column 3 & 4 as per the below result:
| Non-Matching Records | |||
| A | B | Records that are in A, but not in B | Records that are in B, but not in A |
| 1000 | 1000 | 1000 | 17000 |
| 1000 | 5000 | 2000 | |
| 2000 | 17000 | -1000 | |
| 5000 | 3000 | 202.31 | |
| 3000 | |||
| -1000 | |||
| 202.31 | |||
If further explanation is needed, plz let me know. I have also attached the excel file if somebody need to do the working directly in the file.
| Attachment | Size |
|---|---|
| extracting non-matching entries.xls | 24.5 KB |
Try this - it isn't "elegant
Try this - it isn't "elegant but it works"
Private Sub ExtractUnique()
Dim LastRowA As Long, LastRowB As Long, Y1 As Long, Y2 As Long
Dim ANum As Single, DestRow As Integer
DestRow = 20
LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
For Y1 = 3 To LastRowA
ANum = Range("A" & Y1).Value
For Y2 = 3 To LastRowB
If ANum = Range("B" & Y2).Value Then
GoTo SkipA
End If
Next Y2
DestRow = DestRow + 1
Range("C" & DestRow).Value = ANum
SkipA:
Next Y1
For Y1 = 3 To LastRowB
ANum = Range("B" & Y1).Value
For Y2 = 3 To LastRowA
If ANum = Range("A" & Y2).Value Then
GoTo SkipB
End If
Next Y2
DestRow = DestRow + 1
Range("D" & DestRow).Value = ANum
SkipB:
Next Y1
End Sub
PS your original data is wrong, 1000 is in A and B :-)