Comparing two large sets of data from Sheet1 & Sheet1

Status
Not open for further replies.

BigMo1987

New Member
Joined
Jan 11, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hello, I am trying to compared or tied out two set of data from Sheet1 and 2 but each sheets have over 9000 line items. I already have a VBA codes but due to the size of my data excel crashed. Can someone help? Below is the code.
Sub Compare_Difference()

Dim eRow1&, eRow2&
Dim cell1 As Range, cell2 As Range
Dim rngSht1 As Range, rngSht2 As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Application.ScreenUpdating = False

Set ws1 = ActiveWorkbook.Sheets(1)
Set ws2 = ActiveWorkbook.Sheets(2)

' Get last row for each data on Sheet1 and Sheet2
eRow1 = ws1.Range("A1").End(xlDown).Row
eRow2 = ws2.Range("A1").End(xlDown).Row

' Define Range for each sheet
Set rngSht1 = ws1.Range("A2", "A" & eRow1)
Set rngSht2 = ws2.Range("A2", "A" & eRow2)

' Compare list in ws1 to ws2
ws1.Range("D1") = "Diff (Sht2 - Sht1)"
For Each cell1 In rngSht1
For Each cell2 In rngSht2
If cell2 = cell1 And cell2.Offset(0, 1) = cell1.Offset(0, 1) Then
Select Case True
Case ws2.Range("C" & cell2.Row) = ws1.Range("C" & cell1.Row)
ws2.Range("A" & cell2.Row, "C" & cell2.Row).Interior.ColorIndex = 6
Case Else
ws2.Range("A" & cell2.Row, "B" & cell2.Row).Interior.ColorIndex = 6
ws1.Range("D" & cell1.Row) = ws2.Range("C" & cell2.Row) - ws1.Range("C" & cell1.Row)
End Select
End If
Next
Next

' Compare list in ws2 to ws1
ws2.Range("D1") = "Diff (Sht1 - Sht2)"
For Each cell2 In rngSht2
For Each cell1 In rngSht1
If cell1 = cell2 And cell1.Offset(0, 1) = cell2.Offset(0, 1) Then
Select Case True
Case ws1.Range("C" & cell1.Row) = ws2.Range("C" & cell2.Row)
ws1.Range("A" & cell1.Row, "C" & cell1.Row).Interior.ColorIndex = 42
Case Else
ws1.Range("A" & cell1.Row, "B" & cell1.Row).Interior.ColorIndex = 42
ws2.Range("D" & cell2.Row) = ws1.Range("C" & cell1.Row) - ws2.Range("C" & cell2.Row)
End Select
End If
Next
Next

' Sort ws1
With ws1.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending
.SetRange Range("A1", "D" & eRow1)
.Header = xlYes
.Apply
End With

' Sort ws2
With ws2.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending
.SetRange Range("A1", "D" & eRow2)
.Header = xlYes
.Apply
End With

' Return cursor to cell A1 for each worksheet
Application.Goto ws2.Range("A1"), True
Application.Goto ws1.Range("A1"), True

Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,805
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: Comparing 2 datasets for reconciliation using VBA

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,128,167
Messages
5,629,079
Members
416,364
Latest member
maatpsr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top