Compare 2 worksheets and compute differences in 3rd worksheet.

[Maverick24]

Comparison.xlsx

Hi all, I have been spending many hours looking for some code to do some relatively simple comparison tasks, but I haven't been able to find an answer to my particular problem.

I have 2 worksheets "Version 1" and "Version 2", with the same number of columns, but potentially a different number of rows. The structure of each sheet is:

Vendor // Product Line // Sub Product Line // Product Category // Item Number // Item Description // 12 additional columns with the data I would like to compare (titled "MF P1" - "MF P12")

I am looking for some VBA code to compare "Version 1" and "Version 2" worksheets for any changes by ITEM NUMBER and paste any item that has changed in a third worksheet "changes" with some calculations:

1) The Difference between columns H-S
2) The % varriance between columns H-S

I have attached the file, as well as the results I would like sheet 3 to show. The number of rows might be different if there are new item numbers added to the most current version. If there are no changes for the item number it does not need to appear in sheet 3.

Thanks in advance for the help!

[JasperD]

Hi Maverick,

try the code below - worked for me on your sample sheet. Make sure to put it in a module and not as a worksheet code.

Please Login or Register  to view this content.

Please click the * below if this helps

[Maverick24]

Jasper, thank you for your help. It is much appreciated. It is almost exactly what I need but there are a couple issues I noticed that I'm hoping you can help.

1) When comparing the two versions, the macro is placing some values under the DP9 column in the "Change Analysis" sheet that I am unsure where it is pulling from.

2) The macro errors with "Run-time error '11: Division by zero. Is there a way to have it return a (blank) in the percentage columns if it tries to divide by 0?

I have attached a new file with more items so you can test it out if you desire.

Thanks again for your help!

Comparison Test.xlsx

[JasperD]

Hi Maverick,

1) There's actually nothing in the column under DP9. It just appears that way, due to your weird formatting of the analysis sheet. The values you see are actually in column L (under DP5), which makes sense. Fix your formatting and all will be solved.
2) My bad, didn't think of that. Easiest way to solve it would be to put a "On Error Resume Next" line just before the division line. In the code this would look like this :

Please Login or Register  to view this content.

This would just skip the division and leave it blank.

Please really consider to click the * below if this helps, thank you

[Maverick24]

Hi Jasper,

Thanks for the help. Adding the On Error Resume Next solved the divison by 0 error as did the reformatting.

What code would I need to add so that if there is a new Item# in Version 2 that did not appear in Version 1, it copies it over to the "Change Analysis" sheet and basically calculates the difference by subracting 0.

I've clicked the * below You've been very helpful.

[JasperD]

Try this:

Please Login or Register  to view this content.

Please click the .... well, you know the drill

[Maverick24]

Thanks Jasper! One last thing and it works exactly how I had hoped for.

There's a few items that appear on the Change Analysis sheet because there is a very slight difference in versions such as item 11105 which has a deviation of 3.63797880709171E-12. Can we add something in the code that says to only include idems on the Change Analysis sheet where the difference between versions is equal to or greater than 1 case?

Thanks again.