How do I compare two arrays of numeric strings?

[Guest]

I am trying to find matches in two very long columns of digit strings. I
want to create a loop to compare the first digit string in column A to each
digit string in column B and return any matches and then to continue to the
next digit in column A (look for matches in B) and so forth...

 

[James D. Connelly]

See my post (Be Gentle) I think my sub routine would do what you want. NOTE
make sure you copy the raw data into a new book, with the bigger of the two
columns in the first sheet and the smaller column in the second sheet. It
will delete all the found matches in the first sheet leaving only the
"unique" data which you can then use the data sort to get rid of the empty
rows. This would leave you with a list of the data that is unique in the
first sheet. Now if there is data in the second sheet that is unique, I am
not sure what the program would do. I knew in my case that the data in
sheet2 had to be already in sheet1.

Not sure if it helps at all. It might give you a nudge and a hint as to how
to do it, mind you it is slowwwww!

James D. Connelly

 

[Guest]

James,

Thank you. How can I view the sub routine?

 

[Guest]

John,
Here is a "general" routine which compares two columns (lists)
of data - MASTER in column A and UPDATE in column B. Data starts in row 2.

It lists Matches, Master Only and Update only. You can adapt for your own
needs.


HTH

Sub CompareTwoColumns()

Dim rngA As Range
Dim rngB As Range
Dim rw1 As Long
Dim rw2 As Long
Dim rw3 As Long

Dim Start as Double, Finish as Double, TotalTime as Double

Start = Timer ' Set start time.
headings = Array("Master", "Update", "Matched", "Master Only", "Update Only")

Application.ScreenUpdating = False

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet1")

With ws1
Set rngA = .Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
End With

With ws2
Set rngB = .Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
End With



rw1 = 2
rw2 = 2
rw3 = 2

ws1.Columns("C:E").ClearContents
ws1.Range("a1").Resize(1, 5) = headings

' In column A and column B

For Each Cell In rngA
If Not IsError(Application.Match(Cell.Value, rngB, 0)) Then
ws1.Cells(rw1, 3).Value = Cell.Value
rw1 = rw1 + 1
End If
Next

' In column A but not in column B

For Each Cell In rngA
If IsError(Application.Match(Cell.Value, rngB, 0)) Then
ws1.Cells(rw2, 4).Value = Cell.Value
rw2 = rw2 + 1
End If
Next

' In column B but not in column A

For Each Cell In rngB
If IsError(Application.Match(Cell.Value, rngA, 0)) Then
ws1.Cells(rw3, 5).Value = Cell.Value
rw3 = rw3 + 1
End If
Next

Application.ScreenUpdating = True

Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
MsgBox "Ran for " & TotalTime & " seconds"



End Sub

 

[Dave Peterson]

Watch out for your unqualified ranges:

With ws1
Set rngA = .Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
End With

With ws2
Set rngB = .Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
End With

becomes:

With ws1
Set rngA = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

With ws2
Set rngB = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With

(I added dots in front of each Cells() and in front of Rows.count (rows.count
would have worked, though--since there are the same number of rows on each
worksheet.))