Compare 2 Excel files and highlight differences on 3rd file

[gk17]

Hi,

Tried searching here but couldn't find a solution to resolve this. I have two worksheets each with 4 columns. Most of the data are identical but we want to see what the differences are and have them highlighted on a third worksheet. If possible, anything that exists in production and not in test should be highlighted in red and any missing rows from test should be highlighted in green. These new rows inserted and highlights should be on the third worksheet.

I found some solutions online but they don't work to my requirements. I don't want it to compare each respective row since the order will definitely not be the same. I basically want to use something similar to a vlookup to compare each row to the entire worksheet between each other so it's now Row 1 matching with Row 1 only. I also tried doing the compare and merge (which is probably the closest I can get to what I need here) but neither of these are shared workbooks and making them shared workbooks won't work since there has to be some changes tracked over time.

File 1 (Production):
[pre]ID Active Test Name Code
1 Y ABC 8923-1
2 Y DEF 9998-2
3 Y GHI 3892-1[/pre]


File 2 (Test):
[pre]ID Active Test Name Code
1 Y ABC 8923-1
8 Y JKL 3389-0
11 Y MNO 2219-8[/pre]


New File (new row insertion and highlights):
[pre]ID Active Test Name Code
1 Y ABC 8923-1
[COLOR=black green]2 Y DEF 9998-2
3 Y GHI 3892-1[/color]
[COLOR=black red]8 Y JKL 3389-0
11 Y MNO 2219-8[/color][/pre]

I'm using Excel 2016 to do this currently.

Thanks in advance for any help on this.

 

[SkipVought]

Hi

Is ID the only comparator?

How many rows are we talking about?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

So I'd use MS Query to import all rows from each workbook into two separate sheets.

if this is a one time exercise, just copy the two tables into a third sheet and use Remove Duplicates in the Data tab to get all the rows in both tables. Then using the MATCH() function you can determine which IDs are in one, the other or both.

If not I'd use three queries: an inner join indicating rows in both, a left join indicating rows not in the second, and a right join indicating rows not in the first.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's my second solution

[pre]
In Both Production & Test In Production Only In Test Only
ID Active Test Code ID Active Test Code ID Active Test Code
1 Y ABC 8923-1 2 Y DEF 9998-2 8 Y JKL 3389-0
3 Y GHI 3892-1 11 Y MNO 2219-8
[/pre]

In Both Production & Test
SELECT p.*
FROM 
  [Production$] p
, [Test$] t
WHERE (p.ID = t.ID)		
			
In Production Only
SELECT p.*
FROM 
  [Production$] p LEFT OUTER JOIN
  [Test$] t 
 ON p.ID = t.ID
WHERE (t.ID Is Null)			
			
In Test Only			
SELECT t.*
FROM
  [Test$] t LEFT OUTER JOIN
  [Production$] p 
 ON t.ID = p.ID}
WHERE (p.ID Is Null)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]