Compare 2 Worksheets - Show List of Variances on 3rd Sheet

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • You show the data in the New Layout in a completely different order to either the Original or Current data, do you want the New Layout data sorted?

    I see from your code that you are ignoring that error.

    Dataset in Original and Current Data will always be sorted in the same order based on DGF Lane ID (column A), the rest will not be the same order.

  • this might help make my thought process more understandable.

    1. Sheet 1 and Sheet 2 will always be sorted the same way based on Column A (DGF Lane ID)

    2. The amount of columns to be compared will always change but will always start in Column 4 (D).

    - I had some line of code that find the amount of columns there are, maybe we can use that portion ?

    ActiveWorkbook.Sheets(General).Range ("1:1")

    TopLeftAddress = ActSht.Range("1:1").Address 'identify top left cell of the table, to see where the table starts

    LastCol = ActSht.Range(TopLeftAddress).End(xlToRight).Column ' total number of columns in each pivot table

    i = ActSht.Range(TopLeftAddress).Column 'column number where the table starts

    HeaderRow = ActSht.Range("1:1").Row 'row number where the table starts

    3. The headers in Sheet 3 should depend on what they are in Sheet 1/ Sheet2 and should be auto-populated. To identify the numbers in Sheet 3 easier, the header from Sheet1 should get the additional comment "- Original" and the header from Sheet 2 "- Current"

    thanks for the expert help !

  • OK, but your New Layout has the Origin Country and Origin Region sorted and the Lane ID still sequential from 1.

    Is that how you want it, or do you want each row to retain its original Lane ID?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I tried to make two small modifications

    - I deleted the table in the result sheet to have the results start from scratch, e.g. Columns 1, 2,& 3 are added without any calculations and then continue with Column 4 and Origin/Current/Deviation%

    = received an error on code Line 34 : With Result.ListObjects(1)

    - add an additional calculation for a simple Origin minus Current value calculation by adding another condition under code line 22 (

    Else if z(i - 1, iii + 2) = Format(y(i, ii) - x(i, ii) - 1, "0") but received another error.

    any pointers from your side?

  • You are getting the error on Line 34 because you have deleted the Excel Built-in Table (.ListObject(1)) referred to that Table.

    In Line 22 the "Else" and "If" should be one word (ElseIf)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Understood.

    1. what would the code need to be to not require a table in the result sheet (in case a user deletes the table by accident)?

    2. i was able to add the else if statement but am getting a subscript out of range error when adding a column to display the deviation # result.

    For i = 7 To UBound(x, 2)

    ii = ii + 3

    Hdrs(ii) = x(1, i) & " Original"

    Hdrs(ii + 1) = x(1, i) & " Current"

    Hdrs(ii + 2) = x(1, i) & " Deviation %"

    'Hdrs(ii + 3) = x(1, i) & " Deviation #" am getting a out of range error here

    3. i haven't been able to figure out how to start naming the results table with column 1. If someone would start with different column headers, the code should show the columns as they are named in Original or Current.

    Column8 Column9 Column10 Column11 Column12 Random1 Original
    $0.09 New Entry $0.08 $0.17 113% 1.5
    $0.10 New Entry $0.06 $0.16 167% 2
  • I strongly recommend using an Excel Built-in Table rather than a manually created one. They are much easier to work with and have many functions not available for a manual table. They are not easy to delete accidentally, if it does happen then all the user needs to do is highlight all the headers and data, go to Insert on the Ribbon and click "Insert Table". The Table will then be re-established.

    I really cannot say what the code needs to be if you do not use a Built-in Table without seeing your workbook as you have modified it.

    You are getting the error because you have not increased the dimensions of the Hdrs array to account for the extra column.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Understood. I prefer tables as well.

    i re-dim HDRS "ReDim Hdrs(1 To UBound(z, 2) - 11)" but it only adds one additional column to the end and not by block. Reducing it to 10,9 etc only adds empty columns to the end.

    my additional elseif calculation also doesn't produce a calculation.

    and lastly i cannot figure out how to start naming the columns automatically starting with A, Every time i change a parameter i get "subscript out of range" instead of the desired result. Columns 1-12 are just named column and then the previous headers appear.

    any help you could offer in making this work would be greatly appreciated. I can only learn from your corrections.

  • Try this version. I included some color banding in the final Table to make each section more visibly obvious.

    I also removed the instructions from Columns A & B in the Original and Current sheets, replacing them with Data Validation for Column A and a comment for Column B. That allows the code to pick up and use the headers complete and not have to try and remove any instructions.

  • Wonderful. the color identification is great touch. I will now compare v4 to v5 to understand the code modifications better. yesterday evening I was able to add the deviation # column to each block but the headers didn't shift along. so I am very thankful for your help in getting me over this last hill !!!:thumbup::thumbup:

  • You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • KjBox

    if i would want the analysis to start, let's say in Column 6 instead of 4 (C) would i have to change this line to 6?

    For i = 2 To UBound(x, 1)

    iii = 0

    For ii = 4 To UBound(x, 2)

    For ii=6 to Ubound(x,2)

  • Correct.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • i think i need to adjust something else too. The columns towards the left of Column 6 are still getting the Original/Current/Deviation added to them.

  • Do you mean your Original and Current Data tables have additional columns, so look something like the attached?

    If so, will all the columns that do not need to be checked for changes contain Text only (i.e. no numerical entries), apart from column A?

  • correct. so everything in yellow copied over and then analysis should start with column 6

    i am thinking of attaching a pivot analysis to this and would need additional source column data.

  • I was thinking, could that portion be dynamic in a way?

    So that the user could specify the column where the numeric values start ?

    so i think where 4 is mentioned in the code lines below, a link to cell would have to be entered, right ?:

    Code Line 15 For ii = 4 To UBound(x, 2) 'could 4 be linked to cell E1 in the sheet "data_changes"?

    Code Line 16 iii = iii + 4

    Code Line 31 For i = 4 To UBound(x, 2)

    Code Line 31 ii = ii + 4

    changing those give me a subscript out of range error though.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!