Match specific value then highlight differences (by row)

[untitled_1]

Hi I'm looking for a bit of help if possible.

I've got two sheets that are a bit similar. Sheet 1 contains a list of cars, with column F containing a concatenation of certain key fields in that particular row (Make-Model-Country).

Sheet 2 contains a similar list of information and again column F contains the same concatenation, however, other details such as the Colour and Year may be different to sheet 1 (see an example below).

I'm looking for something that firstly does an INDEX MATCH type lookup (between the concatenation in sheet 1 to sheet 2) and then highlight any differences it finds in columns D and E (Colour and Year) on sheet 2 (i.e. if it finds the concatenation but there is blue car with the same concatenation in sheet 1 highlight the new colour in blue). I only want the cells that are different on sheet 2 (to sheet 1) to be highlighted.

Sheet 1 can have up to 10,000 rows and sheet 2 has around 1,000 rows so the rows will not be in the same order on both sheets. I've tried highlighted the differences using conditional formatting and then using a macro to remove the rules (whilst keeping the formatting) but it takes far too long due to run so hoping there is a quicker way.

Is there a macro or VB that could highlight these differences in blue between the two sheets? I'd appreciate any help

 

[Roderick_E]

It would be easier if one of the sheets contained unique entries. For example on sheet1 the Mercedes line is duplicated. If you can get a unique list, we can just loop through that list and highlight on sheet2 anything that does not match

Even with a conditional format perhaps

 

[untitled_1]

Hi Roderick E, unfortunately sheet 1 contains many duplicate rows and sheet 2 may only contain 1 or 2 rows BUT this may have a different colour or year.

I need the differences to be highlighted so I can find the differences on sheet 2 and fix them so they match sheet 1.

That's why I suggest an INDEX MATCH type lookup first and then check to see if the colour/year appears on sheet 1 but I'm not sure how to write that. I've tried the conditional formatting option but it's too slow.

 

[Marcelo Branco]

i suggest two helper columns in Sheet2. Something like this

	H​	I​
1​	Different Color​	Different Year​
2​	0​	0​
3​	1​	0​
4​	0​	0​
5​	1​	0​
6​	0​	1​

Formula in H2 copied down
=--(COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B2,Sheet1!$C:$C,$C2,Sheet1!D:D,"<>"&D2)>0)

Formula in I2 copied down
=--(COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B2,Sheet1!$C:$C,$C2,Sheet1!E:E,"<>"&E2)>0)

Then you can use these helper columns in Conditional Formatting or in a macro to format the cells whose rows = 1

Hope this helps

M.

 

[untitled_1]

Thanks Marcelo, I did consider using helper columns but I really wanted to avoid the conditional formatting - thanks for looking into it.