Macro to tell me if two coulmns are the same

[tonywatsonhelp]

Hi everyone,

if it possible id like a macro that does this

Compares Sheet("Data1") Column J to Sheet("data2") column D
if both columns are identical put then

Sheet("Data1") cell M1 = Yes, if not = No

can this be done?

please help if you can

Thanks

Tony
​

 

[mole999]

is it just they sum the same, because sorting will make them different

 

[footoo]

Sub MatchCol()
Dim r1 As Range, r2 As Range, cel As Range, d1$, d2$
With Sheets("Data1")
    Set r1 = .Range("J1:J" & .Cells(Rows.Count, "J").End(3))
End With
With Sheets("Data2")
    Set r2 = .Range("D1:D" & .Cells(Rows.Count, "D").End(3))
End With
For Each cel In r1
    d1 = d1 & cel
Next
For Each cel In r2
    d2 = d2 & cel
Next
If d1 = d2 Then
    Sheets("Data1").[M1] = "Yes"
Else: Sheets("Data1").[M1] = "No"
End If
End Sub

 

[tonywatsonhelp]

Thank you footoo, this looks like it will do the job thank you
Tony

 

[footoo]

Correction :

Sub MatchCol()
Dim r1 As Range, r2 As Range, cel As Range, d1$, d2$
With Sheets("Data1")
    Set r1 = .Range("J1:J" & .Cells(Rows.Count, "J").End(3)[COLOR=#ff0000].Row[/COLOR])
End With
With Sheets("Data2")
    Set r2 = .Range("D1:D" & .Cells(Rows.Count, "D").End(3)[COLOR=#ff0000].Row[/COLOR])
End With
For Each cel In r1
    d1 = d1 & cel
Next
For Each cel In r2
    d2 = d2 & cel
Next
If d1 = d2 Then
    Sheets("Data1").[M1] = "Yes"
Else: Sheets("Data1").[M1] = "No"
End If
End Sub