COMPARE MULTIPLE CELLS

[Joyce]

I would like to compare: cells A2:E2 to G2:K600

If anything in A2 to E2 does not match to section G thru K, I would like to highlight it.

My sample data is:
Columns A to E:
4600 46 01 999999 Smith, Joe

Compare to
Columns G to K:
4600 10 03 666666 Day, Night
4600 46 02 999999 Smith, Joe
4410 46 01 777777 Night, Day

In this example, I'd like to highlight the 01 in the top Joe Smith line because that is the only item that is different.

Hopefully someone has an idea. Thank you for your help and your time.

 

[fairwinds]

Hi,

The long formula in A7 has to be entered with Ctrl + Shift + Enter.

It shows on which row in the list you get best match. In this example 5 matches on row 2. if there are more "5 matches" only first will be shown.

The on row 4 you will see TRUE / FALSE if that certain cell is matching or not.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	search						List
2	4600	46	1	999999	Smith,	Joe	4600	10	3	666666	Day,	Night
3	Flag						4600	46	2	999999	Smith,	Joe
4	TRUE	TRUE	FALSE	TRUE	TRUE	TRUE	4410	46	1	777777	Night,	Day
5							4600	10	1	666666	Day,	Night
6	Closestmatch						4600	46	5	999999	Smith,	Joe
7	2						4600	46	2	999999	Smith,	Joe
8							4600	46	3	999999	Smith,	Joe
Sheet1

 

[Chitosunday]

Assuming your data in column g to K is from row 1 to 3
Use format, conditional formating ,condition1 formula is
For cell a1:
=INDEX(G$1:G$3,MATCH($E1,$K$1:$K$3))<>A1
then click the format box , bold or anything you like ,click Ok,oK
After exiting
copy the format to cell b1 to d1
then in cell e1, open teh conditional formating then formula is
=COUNTIF($K$1:$K$3,E1)=0
then choose your format like bold,ok,ok

 

[Joyce]

Thank you so much! You solved it so quick when I had been spinning my wheels trying this or that.

I used Chitosunday's version & it is PERFECT! (why didn't I think of that??)

Thanks, again.