Hi Jeff

Sorry, but now I'm really confused. I don't understand what you mean when you say "additional rows with different names appear in the row".

How does a row appear in an existing row?

What I was expecting was that we would do a row by row check for differences. So my question was about identifying how Row X in sheet1 is the same as Row Y in sheet2 and then looking for the other elements in those 2 rows that are different. Here's how I was thinking we would do that;

Create a new Column A in each sheet and populate each row to identify its unique characteristics, For example, if the combination of the first 4 cells of each row is what identifies that row as unique then the cell in Col A would would enter the formula

= B2 & B3 & B4 &B5 ........... and copy this calculation to each row in each sheet

Having done this on each sheet I would then use a vlookup staring in Column AB of sheet2 that would bring the relevant data from sheet1 to its corresponding row in sheet2. It would look something like this

=IFERROR(Vlookup(A2,sheet1!A2:Z5000,11,FALSE),"NEW ROW") ....... this will bring the Type data (you're old column J) from sheet 1 to Sheet 2

Where a row in Sheet2 does not exist in Sheet1 the words "NEW ROW" would appear in cell AB2

You would do this for each data point that you want to compare e.g. to compare Dates in column L (now M because we added that column) the formula in Column AC would be.

=IFERROR(Vlookup(A2,sheet1!A2:Z5000,14,FALSE),"NEW ROW")

Copy these 2 new formulae down to the bottom and then use Conditional formatting to highlight where Sheets1 and Sheets 2 differ.

It might help if you posted a screen shot of a part of your spreadsheet. I understand that this is confidential information so you'd have to put in some dummy data but try to keep as much of the original data as you possibly can.

There are a few other Excel guys on the forum that may have a different approach (probably would) and who may have a better understanding of what your data looks like. Hopefully they will jump in to provide a better solution.

Reports: ·
Posted 3 years ago