Macro to compare data between worksheets

[masterp]

Hi,

I have been doing C + P for most of this data for a long time, however i was wondering if anyone knows a macro to compare data between 2 worksheets and return the data in a 3rd worksheet (copy data from the 2 worksheets + return the difference in the 5th column). The data has been trimmed down - but has about 200 rows and about 100+ columns

Attached is an example

Please Help

Many thanks

Master P

[koda86]

Hello Master P,
I am not a pro in Excel but if you can post your folder I can have a look! Not sure I can help you but at least I can try!

[masterp]

Hello Master P,
I am not a pro in Excel but if you can post your folder I can have a look! Not sure I can help you but at least I can try!

Attached is the file

Many thanks

Master P

[foxbeat]

This method works. It´s compare the data between the two sheets and writes "Equal" or "Not Equal" in the third sheet. It can be easily edited to reach your requirements.


Option Explicit

Sub test()

Dim rng As Range
Dim rng2 As Range
Dim DataSh1 As Variant
Dim DataSh2 As Variant
Dim i As Single
Dim j As Single
Dim Sh1 As String
Dim Sh2 As String
Dim Sh3 As String

Sh1 = "29 Stock"
Sh2 = "29 Stock Adjusted"
Sh3 = "29 Stock Unadj Vs Adj"

Set rng = Sheets(Sh1).Range("A1:Z1000")
Set rng2 = Sheets(Sh2).Range("A1:Z1000")
DataSh1 = rng.Value
DataSh2 = rng2.Value

Sheets(Sh3).Select

For i = 1 To UBound(DataSh1, 2)

For j = 1 To UBound(DataSh1, 1)

If IsEmpty(DataSh1(j, i)) = False Then
If DataSh1(j, i) <> DataSh2(j, i) Then
Cells(j, i) = "Not equal"
Else: Cells(j, i) = "Equal"
End If
End If

Next j

Next i

Set rng = Nothing
Set rng2 = Nothing

End Sub

[masterp]

This method works. It´s compare the data between the two sheets and writes "Equal" or "Not Equal" in the third sheet. It can be easily edited to reach your requirements.


Option Explicit

Sub test()

Dim rng As Range
Dim rng2 As Range
Dim DataSh1 As Variant
Dim DataSh2 As Variant
Dim i As Single
Dim j As Single
Dim Sh1 As String
Dim Sh2 As String
Dim Sh3 As String

Sh1 = "29 Stock"
Sh2 = "29 Stock Adjusted"
Sh3 = "29 Stock Unadj Vs Adj"

Set rng = Sheets(Sh1).Range("A1:Z1000")
Set rng2 = Sheets(Sh2).Range("A1:Z1000")
DataSh1 = rng.Value
DataSh2 = rng2.Value

Sheets(Sh3).Select

For i = 1 To UBound(DataSh1, 2)

For j = 1 To UBound(DataSh1, 1)

If IsEmpty(DataSh1(j, i)) = False Then
If DataSh1(j, i) <> DataSh2(j, i) Then
Cells(j, i) = "Not equal"
Else: Cells(j, i) = "Equal"
End If
End If

Next j

Next i

Set rng = Nothing
Set rng2 = Nothing

End Sub

Hi,

I put the macro in the 29 Stock Unadj Vs Adj, and it returned the value equal in all the columns.

Anyone able to give an alternate solution?

Many thanks