Copy And Compare vs Switch And Compare

[dnguyen1022]

Hi all,

So eventually, I will have two workbooks with a sheet in each containing about 25,000+ rows. The macro that I am trying to write will compare a column value from each row with the other workbook's rows and output a calculated value into a new workbook, so overall I will be dealing with 3 workbooks. Would it be faster to copy over the sheets that are being compared into the newly made workbook and then compare or switch back and forth and compare assuming the two original workbooks had 25,000+ rows of data?

[mdmackillop]

Depends upon your proposed operation. What are you doing with the data?

[dnguyen1022]

Well I'm taking values from the same columns in both workbooks and comparing them against each other for a partial match. One string, I leave as it is. The other, I chop it up into pieces and use the InStr function to see if that piece is inside the string that was left as is. If I see that there is about a 60% match, then I'll combine the two rows that the strings came from into my newly created workbook.

[mdmackillop]

In that case I would read both sets of data into an array, compare corresponding elements and process the result into the third book.

Something like
Option Explicit

Sub DelRows()
Dim Arr1
Dim Arr2
Dim Arr3()
Dim i As Long
Arr1 = Sheets(1).Cells(1, 1).CurrentRegion
Arr2 = Sheets(2).Cells(1, 1).CurrentRegion
ReDim Arr3(UBound(Arr1))
For i = 1 To UBound(Arr1)
If Arr1(i, 1) > Arr2(i, 1) Then Arr3(i) = Arr2(i, 1)
Next
Sheets(3).Cells(1, 1).Resize(UBound(Arr1)) = Application.Transpose(Arr3)
End Sub

[dnguyen1022]

So would I have to create a multidimensional array, since I only want to compare only 1 particular column with another particular column? I also need to be able to take the two rows where the data came from and merge them into a different sheet. From the way I see it, this can only be done by copying the entire row into a multidimensional array, or go back to the sheets and find the data once again.

[mdmackillop]

Can you post a small sample with your comparison routine?