Compare two columns in different workbooks

[Nicolaf]

Hi,

I need a macro to find out if two columns in two different workbooks have the same data.

First column in Workbook1 - Sheet1 has data in range A5:A10, second column in Workbook2 - Sheet2 has data in range A10:A15.

So if range A5:A10 = range A10:A15 then I should get a message saying "data is the same" if not message should say "data is different".

The data must have same order so for example 1,2,3,4,5 in both ranges (and not 1,2,3,4,5 in one range and 1,3,4,2,5 in the other range).

Which VBA code can I use to do I do this?

Thanks,
N.
:think::think:

[Trebor76]

Hi Nicolaf,

Try this:


Option Explicit

Sub Macro1()

Dim varDataMatrix() As Variant 'Declares a dynamic array variable
Dim lngArrayCount As Long
Dim rngMyCell As Range
Dim wbWorkbookOne As Workbook
Dim wbWorkbookTwo As Workbook

Application.ScreenUpdating = False

Set wbWorkbookOne = Workbooks("Book1") 'Assumes the first workbook name is 'Book1' and that it's open in the current session.
Set wbWorkbookTwo = Workbooks("Book2") 'Assumes the second workbook name is 'Book2' and that it's open in the current session.

'First create an array of the values in the desired range of the first workbook.
For Each rngMyCell In wbWorkbookOne.Sheets("Sheet1").Range("A5:A10") 'Workbook one range is A5:A10 on 'Sheet1'.
lngArrayCount = lngArrayCount + 1
ReDim Preserve varDataMatrix(1 To lngArrayCount) 'Append the record to the existing array
varDataMatrix(lngArrayCount) = rngMyCell
Next rngMyCell

lngArrayCount = 0 'Initialise variable

'Loop through Array elements
For Each rngMyCell In wbWorkbookTwo.Sheets("Sheet2").Range("A10:A15") 'Workbook one range is A10:A15 on 'Sheet2'.
lngArrayCount = lngArrayCount + 1
If rngMyCell.Value <> varDataMatrix(lngArrayCount) Then
GoTo QuitRoutinue
End If
Next rngMyCell

'If we get here both datasets have matched.
Set wbWorkbookOne = Nothing
Set wbWorkbookTwo = Nothing
Application.ScreenUpdating = True
Erase varDataMatrix() 'Deletes the varible contents, free some memory
MsgBox "Data is the same.", vbInformation
Exit Sub

QuitRoutinue:

Set wbWorkbookOne = Nothing
Set wbWorkbookTwo = Nothing
Application.ScreenUpdating = True
Erase varDataMatrix() 'Deletes the varible contents, free some memory
MsgBox "Data is different.", vbExclamation

End Sub

Regards,

Robert

[Trebor76]

Here's a less complex though less dynamic solution:


Option Explicit
Sub Macro2()

Dim wbWorkbookOne As Workbook
Dim wbWorkbookTwo As Workbook
Dim lngMyRow As Long
Dim blnAllMatch As Boolean

Application.ScreenUpdating = False

Set wbWorkbookOne = Workbooks("Book1") 'Assumes the first workbook name is 'Book1' and that it's open in the current session.
Set wbWorkbookTwo = Workbooks("Book2") 'Assumes the second workbook name is 'Book2' and that it's open in the current session.

For lngMyRow = 1 To 6 'There are six rows to check
If wbWorkbookOne.Sheets("Sheet1").Range("A" & lngMyRow + 4) <> wbWorkbookTwo.Sheets("Sheet2").Range("A" & lngMyRow + 9) Then
MsgBox "Data is different.", vbExclamation
blnAllMatch = False
Exit For
Else
blnAllMatch = True
End If
Next lngMyRow

If blnAllMatch = True Then
MsgBox "Data is the same.", vbInformation
End If

Set wbWorkbookOne = Nothing
Set wbWorkbookTwo = Nothing
Application.ScreenUpdating = True

End Sub

HTH

Robert

[mancubus]

how about using arrays...



Sub vbax_53997_Compare_Two_Ranges()
Dim i As Long
Dim wb1ws1, wb2ws2
Dim blnSame As Boolean

wb1ws1 = Workbooks("Workbook1.xlsx").Worksheets("Sheet1").Range("A5:A10").Value
wb2ws2 = Workbooks("Workbook2.xlsx").Worksheets("Sheet2").Range("A10:A15").Value

For i = LBound(wb1ws1) To UBound(wb1ws1)
If wb1ws1(i, 1) = wb2ws2(i, 1) Then
blnSame = True
Else
blnSame = False
Exit For
End If
Next i

If blnSame = True Then
MsgBox "data is the same"
Else
MsgBox "data is different"
End If
End Sub


two workbook must be open as Trebor76 indicated.

[snb]

This suffices


Sub M_snb()
msgbox join([transpose(sheet1!A5:A10)])=join([transpose(sheet2!A10:A15)])
end sub

[Nicolaf]

Great thanks!
Nic

:hi::hi: