MACRO to compare two tables and identify changes

[TBJV]

I have two data tables side by side in the same worksheet that need to be compared. For this example, each table is 5 columns wide; A-E for first table and G-K for second table. However, the actual tables being used are more like 30 columns wide. I have attached a sample.

These tables will not be sorted to match each other and may not even have the same amount of rows. The purpose of the macro is to compare the tables and place a comment in column F adjacent the items in columns A.

The macro I need would do several things

1. Determine if the item in column A exists in column G. If it does, proceed to compare the data in the other columns. If it doesn't, add a comment "NEW PART" in column F and highlight the entire new row in the first table only. Do this for all rows in the first table.
2. If the items exist in both tables, the macro would compare all corresponding columns and if any changes are identified, the comment "REVISION" would be placed in column F and the specific changes would be highlighted in yellow.
3. If the items exist in both tables, and no changes are identified, the comment "NO CHANGE" would be placed in column F.

The sample file has a before and after of the desired reults. I have experiemented with VLOOKUP but firstly, we would need to sorth the data and also, the formula can become humongous if the table sizes become 30 columns wide.

Thanks in advance for any help.

[TBJV]

Any help would be appreciated

[jindon]

Try the attached

[TBJV]

Hi jindon,

Thank you for the reply. The macro works great. I was not familiar with ubound but we managed to figure it out as the sample file was just a portion of the worksheet that we are using. We have many more columns so we adjusted the macro to suit.

One problem we found is that with the macro you supplied, if we have less rows of data in the first table than the second table, the macro will highlight the empty rows and state NEW PART when there is nothing there. I have attached a sample using your macro. The empty rows in table 1 should be ignored as I have another way of dealing with this issue. Can you tweak the macro to ignore empty rows in the first table?

Thanks.

[jindon]

Add one more If clause...

Please Login or Register  to view this content.

[TBJV]

Works great... thanks