Compare 2 Excel Worksheets & Show Items Added, Removed & Modified

[puls8]

Hi, I have 2 worksheets in which the worksheet named Existing Items has a table consisting of list of items with their unit price & worksheet named New Items also has a modified table with updated items (some parts might be added, removed, modified or unchanged). I have attached the test workbook (Sample.xlsx) here for your reference.

I am looking for a macro that compares these 2 sheets with the Part Number as a Unique Identifier & displays the result in the following manner:

1. New items added in New Items -> List these items in the Items Added worksheet
2. Items removed from Existing Items -> List these items in the Items Removed worksheet
3. Items whose price has changed in New Items -> List these items in the Price Change worksheet
4. Items whose price has not changed in Existing Items & New Items -> List these items in the Unchanged worksheet
5. A Summary worksheet which shows the count of items added, removed, modified & unmodified

Note that I have already included information in the Existing Items, New Items, Price Change, Unchanged & Summary worksheets just for your reference but this needs to be done by a macro.

Let me know if you need more information.

Thanks!

[watersev]

hi puls8, welcome to Excelforum, please check attachment, press Run button on Existing Items sheet or run code test (ALT+F8, select "test", Run)

[puls8]

Thanks for your quick response watersev! I tested the macro by adding & changing the price of items and it worked perfectly fine.

However, when I tried removing an existing item in the "New Items" sheet (for example: Mobile Phone) & then run the macro, the "Items Removed" sheet shows the summary (which also appears in the "Summary" sheet) instead of showing the list of items removed. Can you fix this? Apart from this, I think everything else works like a charm. Thanks!

[watersev]

please check attachment

[puls8]

You have done it bro. It works perfectly fine now!!! Thanks a million!!!!

[puls8]

Hi watersev, hope you are fine! Your macro has earned me lot of appreciations here at my work but the real credit goes to you mate

I have now a requirement to add some more information & do some formatting to our spreadsheet & would again need your help. I hope you can help me with this. I do not want to flood you with my requirements & therefore would like to bring this to you one at a time. Below is my specific requirement:

I have added a tab named "PO Spend Report (Raw)" with sample data (Ex: Part Number, Quantity, PO Spend, etc.) to our spreadsheet (attached here). Typically, we will run a similar report and paste it in this tab.

This data will probably have duplicate entries (Part Numbers) as seen in the sample table (Ex: Mouse & Speaker are ordered on different dates).

Requirement: A pivot has to be applied on this table to show unique entries in a new tab. This new tab should be created after user clicks on "Run" button & be named as "PO Spend Report (Formatted)". The data in this new tab should be as shown in the sheet.

Note: In the new tab, the column headers have been updated. We need it to be in this format.


I hope I have explained the requirement clearly. Please feel free to revert incase you are not clear on something or need more information.

Thanks once again and hoping you can help me with this. Once this is complete, I will post my next requirement.

Thanks!

[watersev]

please check attachment

[puls8]

Thanks for the quick response! This works perfectly fine watersev

Below is my second requirement:

Requirement: In the "Existing Catalogue" and "Items Removed" tab, the quantity of the items needs to be populated from the tab "PO Spend" that was created in my last requirement. This should be populated under the "Quantity Purchased" column as shown in the spreadsheet (attached).

Note: This new column (Quantity Purchased) should be added only after the "Run" button is clicked.


In the "Price Change" tab, the following columns need to be modified/added:

The "Unit Price" needs to be renamed to "New Unit Price".

Old Unit Price: This new column should be populated from the "Existing Catalogue" tab.

Price Difference: This is calculated with a simple formula (New Unit Price - Old Unit Price).

% Change: This is again a simple formula ((Price Difference / Old Unit Price) * 100). This column needs to show the percentage.

Quantity Purchased: This needs to be populated from the "PO Spend" tab (From the third column "PO Quantity")

Significance: This is calculated with a simple formula (Price Difference * Quantity Purchased)

All these new columns should be populated only after the "Run" button is clicked.

Thank you so much watersev for your patience and support and let me know if you need more clarification on this.

Thanks!

[watersev]

please check attachment

[puls8]

Thanks for the quick response watersev! I tested the macro & it works fine. However, the "Items Removed" tab did not populate the "Quantity Purchased" column. Rest everything is fine.

Below is probably my last requirement:

Our "Summary" tab needs a new look. The current "Summary" tab can be deleted. I have added an alternative "Overview" tab which should include the following information:

Cell C4 : This is the count of items in "Existing Catalogue" tab.

Cell C5 : This is the count of items in "New Catalogue" tab.

Cell B6 (Time Period) : Difference between cell B5 and B4. That is (B5 - B4) in months as shown.

Cell B8 to B11 : Count of "Items Added" (B8), "Price Change" (B9), "Items Removed" (B10), "Price Unchanged" (B11) tab.

Cell B12 : Calculated as follows: Average of all entries in "% Change" column in the "Price Change" tab. This should be in percentage.

Cell A16 : Sum of all entries in the "Significance" column in the "Price Change" tab.

Cell B16 : Average of all the entries in the "Significance" column in the "Price Change" tab. This should be in percentage.

Cell A20 : Same value as cell C4

Cell B20 : Count of items from the "Existing Catalogue" tab where "Quantity Purchased" is greater than 0. (Ex: There are 3 items whose quantity is > 0, Speaker, Keyboard & Mouse in the attached sheet. So the cell B20 in this case would be 3).

Cell A24 : Count of entries excluding the "Unclassified" entry in the "PO Spend" tab. (This would be 3 in our example - Speaker, Keyboard & Mouse).

Cell B24 : Sum of the entries in the "PO Spend in Original Currency" column excluding the "Unclassified" entries in the "PO Spend" tab.

Cell A25 : Count of entries that only include "Unclassified" part numbers in the "PO Spend Report (Raw)" tab. (This would be 2 in our example).

Cell B25 : Should be the same as cell D6 in the "PO Spend" tab.

Cell A26 : Sum of A24 and A25

Cell B26 : Sum of B24 and B25

Thanks a lot watersev for your support!

[watersev]

please check attachment, I'm not sure I got it right for A20 on Overview sheet

[puls8]

Thanks for your response watersev! I did some testing yesterday and found data to be inconsistent in few cells. I will do a thorough test and get back to you. I know you can fix those errors

Thanks for your help!

[puls8]

Hi watersev, I did a test on 2 different sets of data and below are my observations:

1. The "Quantity Purchased" column shows in the "Existing Catalogue" tab before clicking on "Run" button. Moreover, after the "Run" button is clicked, it shows all values as zeros.

2. Subtotals need to be removed from the "Part - Supplier Part Number" column in the "PO Spend" tab.

3. The "Quantity Purchased" column in the "Items Removed" & "Price Change" tabs shows all values as zeros. This should be pulled from the "PO Spend" tab.

4. The "Old Unit Price" column in the "Price Change" tab does now show correct values. The values for these items should be pulled from the "Existing Catalogue" tab. The values in the columns "Price Difference", "% Change" would also change if values in the "Old Unit Price" changes.

5. The "Significance" column in the "Price Change" tab shows all values as zeros. This should be calculated as per the formula: Quantity Purchased * Price Difference.

6. For some reason, the "Overview" tab does not show any of the computed values (values requested in my last post). This does not happen always. It worked for one set of data and not with the other. My first set of data had less number of items in the "Existing Catalogue" & "New Catalogue" tabs (about 40 rows) and my second set of data had about 29,000 rows in these tabs & I could not see any data in the "Overview" tab for this second set of data.

7. The value for "Average percentage price increase" (Cell A12) on the "Overview" tab shows wrong data. This should be the average of "% Change" column in the "Price Change" tab.

8. Cell A16 on the "Overview" tab shows a zero value. This should be the sum of all values in the "Significance" column in the "Price Change" tab. (If point # 5 listed above for Significance is corrected, then probably this will be correct).

9. Cell B16 on the "Overview" tab needs a correction in the formula. The correct formula to compute this is: Sum(A16) / B24

10. Cell B20 in the "Overview" tab shows a zero value. This needs to be the number of unique items from the "Existing Catalogue" tab that habe been requisitioned. If point # 1 listed above is corrected, then probably this will be correct.

11. Cell A24 shows wrong value. This should be the count of unique items in the "PO Spend" tab excluding the "Unclassified" entry.

12. Cell B24 shows wrong value. This should be the sum of values under the "PO Spend in Original Currency" excluding the "Unclassified" entry.

13. Cell A25 is correct. Cell B25 shows the value as zero. This should be calculated like point # 12 above except that this should only be for the "Unclassified" entry.

14. Cell A26 & B26 on the "Overview" tab are dependant on cells A25 and B25.

I hope that you fix these and let me know if you need any clarification.

Thanks for all your help on this!

[watersev]

provided PO spend shows zeroes all the dependent data will be zeroes as well.

How can I test all those issues myself?

[puls8]

Thanks for your response! I will gather some test data and provide it to you shortly for you to check.

Thanks again!

[puls8]

Hi watersev, sorry for the late response. I have provided a link to the sample test spreadsheet with more line items (~30,000).

Link to the sheet: http://goo.gl/seqt1a

I have listed my observations below:

1. The "Quantity Purchased" column shows in the "Existing Catalogue" tab before clicking on "Run" button. Moreover, after the "Run" button is clicked, it shows all values as zeros. I have added another column (Qty) which shows what the correct values should be.

2. Subtotals need to be removed from the "Part - Supplier Part Number" column in the "PO Spend" tab.

3. The "Quantity Purchased" column shows in the "Items Removed" tab shows all values as zeros. I have added a new column (Qty) to show the correct values.

4. The "Quantity Purchased" column shows in the "Price Change" tab shows all values as zeros. I have added a new column (Qty) to show the correct values. Moreover, if this column is corrected, then the "Significance" column will also be correct as per the formula.

Overview Tab:

5. Cell B12 should show its value in percentage.

6. If point # 4 above is corrected, then cell A16 will be corrected I suppose.

7. Cell B16: A correction in the formula is needed. The correct formula is : Sum(A16) / B24

8. Cell B20 shows a zero value whereas the correct value should be 66.

9. Cell A24 needs a correction. Correct value is shown & highlighted.

10. Cell B24 needs a correction. Correct value is shown & highlighted.

11. Cell B25 needs a correction. Correct value is shown & highlighted.


Let me know if you need any clarification.

Thanks again!

[watersev]

hi puls8, please check attachment, run code "test"

Comments:
1. The "Quantity Purchased" column shows in the "Existing Catalogue" tab before clicking on "Run" button - it is not there initially it is created by code (at least in the sample provided originally).
2. Taking into account your explanations for "Overview" B20 I do not understand why it should be 66.

[puls8]

Thanks a lot watersev! Most of the issue are fixed

Please ignore points # 1 and 2 in your last post. It works fine now. However, I came across 3 fields where values are incorrect in the "Overview" sheet.

Cell B12 : (Average percentage price increase) - When I do an average manually (using average formula) on the column "% Change" in the "Price Change" tab, I see the average is rounded off to 1% (whose exact value is "0.00644370676496858" whereas the macro shows the value as "0.644370676496858%".

Cell A16 : This should be the sum of the "Significance" column in the "Price Change" tab (that is, value should be 220).

Cell B16 : If cell A16 is corrected, then B16 will be corrected I suppose. This should be computed as (=Sum(A16) / B24)

Rest everything is perfectly fine! We are almost to the end of achieving success watersev

Thanks again for all your support on this!!!

[watersev]

please check attachment

[puls8]

Thanks for your quick response watersev! I will do one final check on this and get back to you in sometime.

Thanks!

[puls8]

Hi watersev. Apologies for the delay in response. I just got a different set of data and observed some inconsistency in that after running the latest macro. I will soon share the data with you. Thanks!

[puls8]

Hi watersev, I haveincluded a link with different set of data and have highlighted the cells where the macro added wrong data (in red). Columns in yellow are calculated manually by me.

http://goo.gl/KBtzUf


1. Existing Catalog tab : Cells in red are calculated by the macro whereas the correct data is in the "Qty" tab (in yellow).

2. PO Spend tab : This is surprising as this time the macro did not create the pivot at all & this tab was blank. I manually had to create a pivot (Sheet6).

3. Items Removed tab : Incorrect data is highlighted in red & correct data is in yellow in the "Qty" tab.

4. Price Change tab : Again incorrect data is highlighted in red & correct data is in yellow in the "Qty" tab.

5. Overview tab : Incorrect data is in red.


I believe most of the issues are because the "PO Spend" tab did not populate?

Let me know if you need more information.

Thanks again for all the work watersev!

[watersev]

hi there, change the following line in "PO_Spend_Report":

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sh.Range("i1:l" & lrow), Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=target_sh.Range("a1"), TableName:="PT"

to

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="PO Spend Report (Raw)!R1C9:R" & lrow & "C12").CreatePivotTable TableDestination:=target_sh.Range("a1"), TableName:="PT"

[puls8]

Hi watersev, thanks for replying. I replaced the code with the one you have provided. This fixed the PO Spend pivot tab. However, I am still seeing inconsistency in some data which I have highlighted in red. Most of the issues are now fixed. I have provided a link with the updated sheet.

http://goo.gl/uzsLXl

I believe now the issue is only with the Quantity in the various sheets (highlighted in red) and once this is fixed, we are all set to go.

I hope you can help me with this. Thanks again for all your help.

[watersev]

please amend the following lines:

1.
Sub name: Exitems_QP

temp = pt.GetPivotData("PO Quantity", "Part Description", exitems(i, 2))

to

temp = pt.GetPivotData("PO Quantity", "Supplier Part Number", exitems(i, 1))

2.
Sub name: PC_sheet

temp = pt.GetPivotData("PO Quantity", "Part Description", pricechange(i, 2))

to

temp = pt.GetPivotData("PO Quantity", "Supplier Part Number", pricechange(i, 1))

3.
Sub name: ItmRemoved

temp = pt.GetPivotData("PO Quantity", "Part Description", itemsrem(i, 2))

to

temp = pt.GetPivotData("PO Quantity", "Supplier Part Number", itemsrem(i, 1))

Run the code afterwards and check what issues are left over to rectify

[puls8]

Thanks for the update watersev! I replaced the code and 99.9% issues are fixed. Now there is only 1 field where data is incorrect in the "Overview" tab.

The updated sheet with the code changes is at this link : http://goo.gl/jT7lLA

Cell B12 : This is showing the wrong value. This should show the average of the column "% Change" in the "Price Change" tab. This should be shown in percentage with 2 decimal places.

Cell B16 : Value is correct but this should show the value in percentage with 2 decimal places.

Other than this, I think everything else works perfectly fine. Let me know if you need me to send over the file to you. I think there is a minor modification to be done in the code. Thanks again for your continued support on this!

[watersev]

please replace Overview code with:

Private Sub Overview(exitems_lrow, newitems_lrow, ir, ia, un, pc, dic)

Dim sh As Worksheet, data, po_spend_lrow As Long, pc_counter As Long
Dim pc_arr, i As Long, sum_price_increase As Double, sum_price_diff As Double, sum_q_purchased As Double, sum_significance As Double
Dim exitems_arr, icounter As Long, po_spend_raw_arr, po_spend_raw_lrow As Long

Set sh = Sheets("Overview")

sh.AutoFilterMode = 0

data = sh.Range("a1:c36 ")

data(4, 3) = exitems_lrow - 1
data(5, 3) = newitems_lrow - 1

data(6, 2) = "=Text(Round(Days360(B4, B5) / 30, 0),""#"") & "" month(s) ago"""

data(8, 2) = ia
data(9, 2) = pc - 1
data(10, 2) = ir
data(11, 2) = un

pc_arr = Sheets("Price Change").Range("a1:h" & pc)

For i = 2 To pc
    If pc_arr(i, 6) <> "" Then
        sum_price_increase = sum_price_increase + pc_arr(i, 6)
        pc_counter = pc_counter + 1
    End If
    sum_price_diff = sum_price_diff + pc_arr(i, 5)
    sum_q_purchased = sum_q_purchased + pc_arr(i, 7)
    sum_significance = sum_significance + pc_arr(i, 8)
Next

On Error Resume Next

data(12, 2) = sum_price_increase / pc_counter

data(20, 1) = data(4, 3)

exitems_arr = Sheets("Existing Catalogue").Range("a1:d" & exitems_lrow)

For i = 2 To exitems_lrow
    If exitems_arr(i, 4) > 0 Then
        If Not dic.exists("#" & exitems_arr(i, 1)) Then
            dic("#" & exitems_arr(i, 1)) = i
            icounter = icounter + 1
        End If
    End If
Next

data(20, 2) = icounter

po_spend_lrow = Sheets("PO Spend").Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("PO Spend").PivotTables("PT")
    .PivotFields("Supplier Part Number").PivotItems("Unclassified").Visible = False
    data(24, 2) = .GetPivotData("PO Spend in Original Currency")
    data(24, 1) = Sheets("PO Spend").Cells(Rows.Count, 2).End(xlUp).Row - 2
    .PivotFields("Supplier Part Number").PivotItems("Unclassified").Visible = True
    data(26, 2) = .GetPivotData("PO Spend in Original Currency")
    data(25, 2) = data(26, 2) - data(24, 2)
End With
data(16, 1) = sum_significance
data(16, 2) = data(16, 1) / data(24, 2)
On Error GoTo 0

po_spend_raw_lrow = Sheets("PO Spend Report (Raw)").Cells(Rows.Count, 1).End(xlUp).Row
po_spend_raw_arr = Sheets("PO Spend Report (Raw)").Range("i1:i" & po_spend_raw_lrow)

icounter = 0

For i = 2 To po_spend_raw_lrow
    If po_spend_raw_arr(i, 1) = "Unclassified" Then icounter = icounter + 1
Next

data(25, 1) = icounter
data(26, 1) = data(24, 1) + data(25, 1)

sh.Range("b12,b16").NumberFormat = "0.00%"

sh.Range("a1:c36") = data

End Sub

[puls8]

Hi watersev, apologies for the delayed response. I was a bit stuck with different data sets & stuff. I replaced the above code and it now works 100% fine.

I have no words to thank you for the interest & efforts that you have put in this project. There were many obstacles in the way but you did it again.

Thanks again!