Comparing Column Data

[Charles Linquist]

So here's what I want to do.

Compare columns A & B in sheet 1 with Columns C & D in sheet 2.

if there is a match between the to groupings, copy that data to sheet3 and
if no match, move to sheet 4 or 5.

so here's a example of the data.

Column A Column B Column C Column D
9127 -1451 9104 2123
V#5257 -872 9105 138
9108 -11015 9106 171
V#5257 -872 9107 963
9148 -15022 9108 11015


So out of the data given and ignoring the sign of the number, the only match
is 9108, 11015 in Columns a,b Row 3, and columns CD in row 5.

So I will then need to move the data to the appropriate sheet based on the
results of my comparison. For example: AB FOUND/CD
NOT FOUND, AB NOT FOUND/CD FOUND, AB/CD FOUND.

Any assistance that can be provided will be greatly appreciated. If you need
clarification, just let me know.

Thanks in advance.

Charles

 

[Dick Kusleika]

Charles

I wasn't sure how you wanted your output to look, so I just made a row on
sheet3 with the information. We can change that if you want. If there's
not many rows, you can use a nested loop like below. If you there are a lot
of rows, this may take too long and you may want to use the Find method.
Give this a try and let me know if you need it adjusted.

Sub CompareCols()

Dim Rng1 As Range
Dim Rng2 As Range
Dim Cell1 As Range
Dim Cell2 As Range
Dim Dest As Range

Set Rng1 = Sheet1.Range("A1", Sheet1.Range("a1").End(xlDown))
Set Rng2 = Sheet2.Range("C1", Sheet2.Range("C2").End(xlDown))

For Each Cell1 In Rng1.Cells
For Each Cell2 In Rng2.Cells
If Cell1.Value = Cell2.Value Then
If Abs(Cell1.Value) = Abs(Cell2.Value) Then
Set Dest = Sheet3.Range("A65536").End(xlUp).Offset(1, 0)
Cell1.Resize(, 2).Copy Dest
Cell2.Resize(, 2).Copy Dest.Offset(0, 2)
End If
End If
Next Cell2
Next Cell1

End Sub