Compare two sheets for differences with results on sheet 3

[BrutalDawg]

Hello all,

I am trying to develop a script that would compare sheet 2 to sheet 1, with differences on sheet 3. I am trying to find out when my customers are advancing orders within standard time. Below is the small piece of last two order sheets.

Sheet1:
[TABLE="width: 456"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 661"]
<tbody>[TR]
[TD]Where[/TD]
[TD]Received[/TD]
[TD]Order#[/TD]
[TD]SKU[/TD]
[TD]Status[/TD]
[TD]Quantity[/TD]
[TD]Due[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]1/5/2017[/TD]
[TD]1008[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]27[/TD]
[TD]1/16/2017[/TD]
[/TR]
[TR]
[TD]Houston[/TD]
[TD]1/5/2017[/TD]
[TD]1017[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]18[/TD]
[TD]1/16/2017[/TD]
[/TR]
[TR]
[TD]SA[/TD]
[TD]1/5/2017[/TD]
[TD]1013[/TD]
[TD]pt9261[/TD]
[TD]Firm[/TD]
[TD]21[/TD]
[TD]1/16/2017[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]1/5/2017[/TD]
[TD]1008[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]26[/TD]
[TD]1/23/2017[/TD]
[/TR]
[TR]
[TD]Houston[/TD]
[TD]1/5/2017[/TD]
[TD]1017[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]20[/TD]
[TD]1/23/2017[/TD]
[/TR]
[TR]
[TD]SA[/TD]
[TD]1/5/2017[/TD]
[TD]1013[/TD]
[TD]pt9261[/TD]
[TD]Firm[/TD]
[TD]19[/TD]
[TD]1/23/2017[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2:
[TABLE="width: 661"]
<tbody>[TR]
[TD]Where[/TD]
[TD]Received[/TD]
[TD]Order#[/TD]
[TD]SKU[/TD]
[TD]Status[/TD]
[TD]Quantity[/TD]
[TD]Due[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]1/12/2017[/TD]
[TD]1008[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]29[/TD]
[TD]1/16/2017[/TD]
[/TR]
[TR]
[TD]Houston[/TD]
[TD]1/12/2017[/TD]
[TD]1017[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]20[/TD]
[TD]1/16/2017[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]1/12/2017[/TD]
[TD]1008[/TD]
[TD]pt9261[/TD]
[TD]Firm[/TD]
[TD]8[/TD]
[TD]1/16/2017[/TD]
[/TR]
[TR]
[TD]SA[/TD]
[TD]1/12/2017[/TD]
[TD]1013[/TD]
[TD]pt9261[/TD]
[TD]Firm[/TD]
[TD]25[/TD]
[TD]1/16/2017[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]1/12/2017[/TD]
[TD]1008[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]25[/TD]
[TD]1/23/2017[/TD]
[/TR]
[TR]
[TD]Houston[/TD]
[TD]1/12/2017[/TD]
[TD]1017[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]22[/TD]
[TD]1/23/2017[/TD]
[/TR]
[TR]
[TD]SA[/TD]
[TD]1/12/2017[/TD]
[TD]1013[/TD]
[TD]pt9261[/TD]
[TD]Firm[/TD]
[TD]21[/TD]
[TD]1/23/2017[/TD]
[/TR]
</tbody>[/TABLE]

I would like a way to see the new additions and additional quantities from the new order (sheet 2), is that possible? For reference, the only important part to me is the where, sku, quantity, and due.

Any format would be fine, I would just like to see the net change made. maybe sheet 3 could take all info from sheet 2 and next column would be change? It does not need to say new, just a numerical value will work.
[TABLE="width: 761"]
<tbody>[TR]
[TD]Where[/TD]
[TD]Received[/TD]
[TD]Order#[/TD]
[TD]SKU[/TD]
[TD]Status[/TD]
[TD]Quantity[/TD]
[TD]Due[/TD]
[TD]Change[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]1/12/2017[/TD]
[TD]1008[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]29[/TD]
[TD]1/16/2017[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Houston[/TD]
[TD]1/12/2017[/TD]
[TD]1017[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]20[/TD]
[TD]1/16/2017[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]1/12/2017[/TD]
[TD]1008[/TD]
[TD]pt9261[/TD]
[TD]Firm[/TD]
[TD]8[/TD]
[TD]1/16/2017[/TD]
[TD]NEW! 8[/TD]
[/TR]
[TR]
[TD]SA[/TD]
[TD]1/12/2017[/TD]
[TD]1013[/TD]
[TD]pt9261[/TD]
[TD]Firm[/TD]
[TD]25[/TD]
[TD]1/16/2017[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD]1/12/2017[/TD]
[TD]1008[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]25[/TD]
[TD]1/23/2017[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]Houston[/TD]
[TD]1/12/2017[/TD]
[TD]1017[/TD]
[TD]pt9258[/TD]
[TD]Firm[/TD]
[TD]22[/TD]
[TD]1/23/2017[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]SA[/TD]
[TD]1/12/2017[/TD]
[TD]1013[/TD]
[TD]pt9261[/TD]
[TD]Firm[/TD]
[TD]21[/TD]
[TD]1/23/2017[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


All help is appreciated. No need to follow format, just an idea.

 

[rpaulson]

this willwrite any records that do not match to sheet3

you need to add a Helper worksheet. make sure you nanme it Helper, you can hide it if you want to.

Sub do_it()

Dim ws1 As Worksheet, ws As Worksheet, ws3 As Worksheet, ws4 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")
Set ws4 = Worksheets("Helper")

ws3.Cells().ClearContents
ws4.Cells().ClearContents

wr = 1 'this will biul a List of "INdexNumbers" on sheet 4
For r = 2 To ws1.Range("A" & Rows.Count).End(xlUp).Row
ws4.Cells(wr, "A") = ws1.Cells(r, "A") & ws1.Cells(r, "D") & ws1.Cells(r, "F") & ws1.Cells(r, "G")
ws4.Cells(wr, "B") = ws1.Cells(r, "F") 'qty
wr = wr + 1
Next r


For r = 2 To ws2.Range("A" & Rows.Count).End(xlUp).Row

ino = ws2.Cells(r, "A") & ws2.Cells(r, "D") & ws2.Cells(r, "F") & ws2.Cells(r, "G")

If WorksheetFunction.CountIf(ws4.Range("A:A"), ino) = 0 Then 'add record as something changed

lr = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws3.Rows(lr).EntireRow.Value = ws2.Rows(r).EntireRow.Value
ws3.Cells(lr, "H") = qty

End If

Next r

End Sub

hth,
Ross

 

[BrutalDawg]

This worked great! Thank you!

Is there a way to note the difference on sheet 3? Or only write to sheet 3 if sheet 2 qty is > sheet 1 qty ?

 

[AndrwT]

Hello everyone;

I've been now spending 2 days looking for codes similar to these, however since I am quite noob with VBA I am finding hard to modify it to my needs.

Long story short; we are going to migrate platforms and once the new platform is gone live, we will want to compare data from platform 1 to platform 2.

I therefore have 2 workbooks in which I have a set of data identified by a unique number in column A.

What I want is that the macro identifies the changes between Platform A (Workbook A - Sheet 1 "XXX") and Platform B (Workbook B - Sheet 1 "YYY").

Until now I have found a code that does it, but only if the unique values are sorted the same way; if I add a row in the middle (or remove one), all the values below that row will highlight as error and it's not correct; since what I want is the macro to search for the unique identifier (let's say cell A2 from Workbook Platform A) and look for it in Workbook Platform B and check if all the following columns referring to that identifier are the same or not.

Example (to make it easy):

[TABLE="width: 516"]
<colgroup><col span="2"><col span="4"></colgroup><tbody>[TR]
[TD="colspan: 2"]Workbook 1 - Platform A[/TD]
[TD]ID[/TD]
[TD]Client[/TD]
[TD]Country[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]000001[/TD]
[TD]Google[/TD]
[TD]US[/TD]
[TD]50000[/TD]
[/TR]
[TR]
[TD]000002[/TD]
[TD]Microsoft[/TD]
[TD]UK[/TD]
[TD]72000[/TD]
[/TR]
[TR]
[TD]000003[/TD]
[TD]Oracle[/TD]
[TD]UAE[/TD]
[TD]35000[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]Workbook 2 - Platform B[/TD]
[TD]ID[/TD]
[TD]Client[/TD]
[TD]Country[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]000001[/TD]
[TD]Google[/TD]
[TD]US[/TD]
[TD]50000[/TD]
[/TR]
[TR]
[TD]000002[/TD]
[TD]Microsoft[/TD]
[TD]UK[/TD]
[TD]68000[/TD]
[/TR]
[TR]
[TD]000003[/TD]
[TD]Oracle[/TD]
[TD]UAE[/TD]
[TD]35000
[/TD]
[/TR]
</tbody>[/TABLE]


IF the Cocacola row would have not been included, my current code would highlight the 68000 of Microsoft which has changed compared to Platform A; however, adding the row, all rows below are highlighted because the macro doesn't recognize the same data based on the "location" in the sheet.

in this case I would like the macro to go one step further, find the 000002 for Microsoft, and let me know (either in a different sheet or the same one in a different column or color coded) and tell me that the Revenue has changed.

FYI, my current code is:

Sub RunCompare()


Call compareSheets("ISM", "Planview")


End Sub




Sub compareSheets(shtISM As String, shtPlanview As String)


Dim mycell As Range
Dim mydiffs As Integer


'For each cell in sheet2 that is not the same in Sheet1, color it yellow
For Each mycell In ActiveWorkbook.Worksheets(shtPlanview).UsedRange
If Not mycell.Value = ActiveWorkbook.Worksheets(shtISM).Cells(mycell.Row, mycell.Column).Value Then

mycell.Interior.Color = vbYellow
mydiffs = mydiffs + 1

End If
Next


'Display a message box to demonstrate the differences
MsgBox mydiffs & " differences found", vbInformation


ActiveWorkbook.Sheets(shtPlanview).Select


End Sub

If anything is unclear please let me know, it is sometimes complicated to explain in words, just like BrutalDawg was mentioning.

Thanks all!!

 

[AndrwT]

Sorry, just seen I pasted a wrong data set:

[TABLE="width: 322"]
<colgroup><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 4"]Workbook 1 - Platform A[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Client[/TD]
[TD]Country[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]000001[/TD]
[TD]Google[/TD]
[TD]US[/TD]
[TD]50000[/TD]
[/TR]
[TR]
[TD]000002[/TD]
[TD]Microsoft[/TD]
[TD]UK[/TD]
[TD]72000[/TD]
[/TR]
[TR]
[TD]000003[/TD]
[TD]Oracle[/TD]
[TD]UAE[/TD]
[TD]35000[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]Workbook 2 - Platform B[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Client[/TD]
[TD]Country[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]000001[/TD]
[TD]Google[/TD]
[TD]US[/TD]
[TD]50000[/TD]
[/TR]
[TR]
[TD]000005[/TD]
[TD]Cocacola[/TD]
[TD]SG[/TD]
[TD]150000[/TD]
[/TR]
[TR]
[TD]000002[/TD]
[TD]Microsoft[/TD]
[TD]UK[/TD]
[TD]68000[/TD]
[/TR]
[TR]
[TD]000003[/TD]
[TD]Oracle[/TD]
[TD]UAE[/TD]
[TD]35000
[/TD]
[/TR]
</tbody>[/TABLE]


This is the one