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

Quote from KjBox

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?

Each row should retain the original row ID and ideally sheet three shld be sorted the same way with lane ID increasing starting with 1.

Try this version

Wonderful. This is spot on. Cannot express my appreciation for your help enough !!

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)

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.

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.

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 !!!

You're welcome

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.

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.