Compare two sheets for differences with results on sheet 3

BrutalDawg

New Member
Joined
Jun 10, 2015
Messages
39
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:

<tbody>
</tbody>
WhereReceivedOrder#SKUStatusQuantityDue
Dallas1/5/20171008pt9258Firm271/16/2017
Houston1/5/20171017pt9258Firm181/16/2017
SA1/5/20171013pt9261Firm211/16/2017
Dallas1/5/20171008pt9258Firm261/23/2017
Houston1/5/20171017pt9258Firm201/23/2017
SA1/5/20171013pt9261Firm191/23/2017

<tbody>
</tbody>


Sheet2:
WhereReceivedOrder#SKUStatusQuantityDue
Dallas1/12/20171008pt9258Firm291/16/2017
Houston1/12/20171017pt9258Firm201/16/2017
Dallas1/12/20171008pt9261Firm81/16/2017
SA1/12/20171013pt9261Firm251/16/2017
Dallas1/12/20171008pt9258Firm251/23/2017
Houston1/12/20171017pt9258Firm221/23/2017
SA1/12/20171013pt9261Firm211/23/2017

<tbody>
</tbody>

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.
WhereReceivedOrder#SKUStatusQuantityDueChange
Dallas1/12/20171008pt9258Firm291/16/20172
Houston1/12/20171017pt9258Firm201/16/20172
Dallas1/12/20171008pt9261Firm81/16/2017NEW! 8
SA1/12/20171013pt9261Firm251/16/20170
Dallas1/12/20171008pt9258Firm251/23/2017-1
Houston1/12/20171017pt9258Firm221/23/20172
SA1/12/20171013pt9261Firm211/23/20170

<tbody>
</tbody>


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

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,143
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.


Code:
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

New Member
Joined
Jun 10, 2015
Messages
39
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

New Member
Joined
Mar 30, 2016
Messages
29
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):

Workbook 1 - Platform AIDClientCountryRevenue
000001GoogleUS50000
000002MicrosoftUK72000
000003OracleUAE35000
Workbook 2 - Platform BIDClientCountryRevenue
000001GoogleUS50000
000002MicrosoftUK68000
000003OracleUAE35000

<colgroup><col span="2"><col span="4"></colgroup><tbody>
</tbody>


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

New Member
Joined
Mar 30, 2016
Messages
29
Sorry, just seen I pasted a wrong data set:

Workbook 1 - Platform A
IDClientCountryRevenue
000001GoogleUS50000
000002MicrosoftUK72000
000003OracleUAE35000
Workbook 2 - Platform B
IDClientCountryRevenue
000001GoogleUS50000
000005CocacolaSG150000
000002MicrosoftUK68000
000003OracleUAE35000

<colgroup><col span="2"><col span="2"></colgroup><tbody>
</tbody>


This is the one :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

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