Excel - Find the odd one(s) out 3

[Livia8]

I have a list which comprises of 3 columns (i.e. ID numbers). Each row should contain the same information on each of the columns (so the same ID should appear on A, B, and C). I'm trying to find a formula that will tell me which of the cells in a row is different from the others, but not if the cell is blank. I've been messing around with IF and MATCH and LOOKUP formulas without much success - it's likely that the solution is staring at me in the face and I'm just doing something wrong.

The closest thing I was able to find was to go to "Edit", "Go to", "Special" and "Row Differences" and then highlighting the selected cells.

Is there a formula that I could use? Even a "conditional formatting" would do, if I could get it to work across more than one column at a time.

Thanks.

Livia

 

[KenWright]

Limited testing:-

Data in A3:C100

Select all data and do Format / Conditional Formatting, then use 'Formula Is' and put in

=AND(NOT(ISBLANK(A3)),LEN(SUBSTITUTE(SUBSTITUTE($A3&$B3&$C3,A3,"["),"[",""))<>0)

and choose a pattern fill colour.

Will highlight ALL cells in the row (Except Blanks) that are not equal, as there is no way of knowing which are right or wrong.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Livia8]

Ken,

I tried but: a) the cond formatting highlights ALL the cells that don't match the cell adjacent to them, and the formula only tells me if a,b, and c are equal to each other but not which one is different. Am I doing somethins wrong?

If it's of any help, this was the closest I got to the right formula before I posted my question:

=(IF(A2=B2=C2,"OK",(IF(A2=B2,"check c",(IF(B2=C2,"check a",(IF(A2=C2,"check b","check all"))))))))

However, if a, b, and c all contain the same data, the formula result is "Check C".

Thanks again.

Livia

 

[UniqueFD]

Livia,

You only need a simple change to your formula to get it to return what you want:

=IF(AND(A2=B2,B2=C2),"OK",IF(A2=B2,"Check C",IF(B2=C2,"Check A",IF(A2=C2,"Check B","Check All"))))

(By the way, you don't need brackets around the IF function.)

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[Livia8]

Tony,

That worked very well, thank you!

Ken,

thanks for your help anyway, i'm sure I'll be able to use your formula for something else.

Regards,

Livia

 

[UniqueFD]

Livia, I'm pleased that's helped, but now that I've properly tested the formula I see a problem: It isn't ignoring blank cells. If you have two blank cells in a row, the formula will indicate the other (valid) cell needs checking, and it will tell you to check blank cells if the other two match.

Not quite a solution, yet. Are you content with the way it works, or do you want it refining?

(Keb, brilliant lateral thinking, using SUBSTITUTE and LEN, by the way)

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[KenWright]

The formula is for Conditional Formatting only, and will not work as just a formula in a cell. Given that is CF, there is no point in having any result be something like the "Check All" bit that you put in, as it is meaningless to CF.

I just tried again and it works fine for me.

Assuming data is in A3:C30

Data looks like this (Without the "<<<" bits at the moment though):-

	A	B	C	
1  				
2  				
3	sdvhdsg	sdvhdsg	sdvhdsg	
4	d      	       	sdvhdsg	<<<
5	sdvhdsg	sdvhdsg		
6	       	sdvhdsg	sdvhdsg	
7	d      	       	sdvhdsg	<<<
8	sdvhdsg	sdvhdsg	sdvhdsg	
9	       	sdvhdsg	sdvhdsg	
10	sdvhdsg	       	       	
11	sdvhdsg	d      	sdvhdsg	<<<
12	sdvhdsg	sdvhdsg	sdvhdsg	
13	       	sdvhdsg	sdvhdsg	
14	sdvhdsg	d      	       	<<<
15	sdvhdsg	sdvhdsg	sdvhdsg	
16	       	sdvhdsg	sdvhdsg	
17	sdvhdsg	       	       	
18	sdvhdsg	sdvhdsg	sdvhdsg	
19	sdvhdsg	sdvhdsg	sdvhdsg	
20	       	sdvhdsg	sdvhdsg	
21	sdvhdsg	sdvhdsg	          	
22	sdvhdsg	sdvhdsg	sdvhdsg	
23	sdvhdsg	sdvhdsg	sdvhdsg	
24	d     	zjsdgg	zjsdgg	<<<
25	zjsdgg	zjsdgg	zjsdgg	
26	      	zjsdgg	      	
27	zjsdgg	zjsdgg	zjsdgg	
28	d     	      	zjsdgg	<<<
29	zjsdgg	zjsdgg	zjsdgg	
30	d     	      	zjsdgg	<<<

I then select A3:C30, do Format / Conditional Formatting, change Cell value is to Formula is using the dropdown, and then put in the formula I gave you, hit the format button, choose a pastel colour for PATTERN and then hit OK.

All the entries where you see "<<<" next to them are the ones that get flagged.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Livia8]

Tony,

At this stage I'm not too concerned about the blanks, I'm just happy it's spotting the rest of the differences - for the blanks I suppose I can use a good old conditional formatting.

Thanks again,

Livia

 

[UniqueFD]

Ken,
Your solution is outstanding and highlights any rows with mis-matching data (but ignores blanks). A star for you!

Livia,
Here's an alternative (which ignores blanks):

=IF(MID(COUNTIF(A2:C2,A2)&COUNTIF(A2:C2,B2)&COUNTIF(A2:C2,C2),1,1)="1","Check A; ","")&IF(MID(COUNTIF(A2:C2,A2)&COUNTIF(A2:C2,B2)&COUNTIF(A2:C2,C2),2,1)="1","Check B; ","")&IF(MID(COUNTIF(A2:C2,A2)&COUNTIF(A2:C2,B2)&COUNTIF(A2:C2,C2),3,1)="1","Check C","")

This is another 'skinning the cat' thing.

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[Livia8]

Guys,

Now I'm almost scared, all of your solutions are impressive!

Ken, I obviously was doing something wrong because now it's working fine.

One more star each, and many thanks again!

Livia

 

[Livia8]

Guys,

Now I'm almost scared, all of your solutions are impressive!

Ken, I obviously was doing something wrong because now it's working fine, thanks.

One more star each, and many thanks again!

Livia

 

[KenWright]

You're welcome.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------