Macro to Compare three columns between two tabs and display differences in a third

Hi everyone!

I am working on a workbook where I want to create a macro to compare the differences between two reports (place in two tabs) on a spreadsheet that have three columns of 1000's of data. That's three columns of data in one table on one tab and three columns of data in another table in another tab.

I may not be explaining the best way to do this, but basically, I have data from inventory I took about a month ago and I want to determine the differences (additions and removals, and if there is no change) from that report to another report I generated about a year ago; so I can clearly see the changes (additions and removals, and if there is no change)

The data will not necessarily match in number of rows and will not be in order so it cannot be compared side-by-side. I would like the results to be displayed in a 3rd tab on the same workbook that will show the results of the matching three columns and then in a fourth column display whether it was added (something added to the inventory since a year ago) or removed (something removed from inventory since a year ago) or if there was no change. So I can easily spot and sort the deltas on the third tab.

Thanks for your help in advance!

here is an example of what the columns might look like

sheet 1 Report from a year ago
A B C
Order# Name QTY
001 Banana 5
001 Banana 4
002 Grape 5
002 Orange 1

Sheet 2 Report Recently pulled (In my example I put the changes in bold red so you can see what I changed/Added)
A B C
Order# Name QTY
002 Apple 5
003 Banana 4
001 Banana 5
002 Orange 8
005 Watermelon 2

sheet 3 Delta Results (additions/removals and if there was no changes on sheet 2 when compared to sheet 1)
A B C D
Order# Name QTY Delta
001 Banana 5 No Change
002 Apple 5 Added
001 Banana 4 Removed
002 Grape 5 Removed
002 Orange 1 Removed
003 Banana 4 Added
002 Orange 8 Added
005 Watermelon 2 Added

Hi There,

Would you be able to clarify what you mean by the change status? Is it in terms of the quantity? How many rows of data do you have? This could potentially change how I solution it (i.e. if optimization is necessary)

Thanks,

Jim

Hi Jim,

Thank you for your response!

I have attached an excel spreadsheet to help clarify what I am trying to ask. I have 3 sheets. Report1, Report2 and Delta. I basically want to compare the columns from sheet "Report2" with the columns from sheet "Report1" and copy the rows from both sheets to the third sheet labeled "Delta" and in that sheet it will put the Delta status from comparing Report2 with Report1 in the "Delta" column by returning "No Change" if there are rows between both sheets that have all 3 matching columns. If there are rows between both sheets that do not have matching columns I would like the formula to display the data on the "Delta" tab with the words "Added" or "Removed" in the "Delta" column to reflect whether the columns in a row in Report2 mismatch the columns in a row in Report1 because the information has since been "Added" to Report2 but was not in Report1 .. or was omitted or "Removed" in Report2 when I ran that report but it was originally in Report1.

Essentially I am trying to track the changes between both reports based on 3 columns, but I'd also like to put the results on the third sheet with a status of what the changes were. Duplicates are okay. if three rows were added to Report2 that were not on Report1 (with the columns not matching in Report1) then the Delta Sheet would copy those three rows to the "Delta" tab and return "Added" for all three rows on the "Delta" column for the status.

I see. Thank you for the clarification. How many rows of data do you have currently and how many will you be getting in the future?

Originally Posted by asianbankguy

I see. Thank you for the clarification. How many rows of data do you have currently and how many will you be getting in the future?

about 5,000 to 10,000