3 cells - Return a value in the 4th cell if they match or are different

[H3MSY]

Hi,

I have done a search but cant quite get what I need - Hopefully this is pretty simple for the resident experts!!

I have multiple rows of 3 columns (A/B/C) that could be blank or could contain 3 or 4 different sets of text. They start out as blanks and don't have to be populated as such.

What I want in column D is a formula that looks at A,B & C and if they have text in them and that text is different return the text "diff" otherwise "ok"

examples
row 1 could be: (blank), Red, Red = ok
row 2 could be: Red, Red, Red = ok
row 3 could be: Red, blue, blue = Diff
row 4 could be: (blank), (blank), blue = ok

I think its the blank cells that potentially going to throw a spanner in the works. I guess the formula needs to 'ignore blanks' somehow,

Many thanks for any help given

 

[etaf]

i think this answers

Compare text values in mutiple columns, and ignore cells

Can someone please help provide a formula to comparing text values in multiple columns, for differences, and ignore blanks For example below: if the values (excluding blanks) in B,C,D,E match then F = "OK" if the values (excluding blanks) in B,C,D,E do not match then F = "Diffrerence" A B...

www.mrexcel.com

just modifying the range

Book2
	A	B	C	D	E	F	G
1
2		red	red	OK		row 1 could be: (blank), Red, Red = ok
3	red	red	red	OK		row 2 could be: Red, Red, Red = ok
4	red	blue	blue	Difference		row 3 could be: Red, blue, blue = Diff
5			blue	OK		row 4 could be: (blank), (blank), blue = ok
6
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=IF(COUNTA(A2:C2)-COUNTIF(A2:C2,LOOKUP(2,1/(A2:C2<>""),A2:C2))=0,"OK","Difference")

or from the link - sumproduct

Book2
	A	B	C	D	E	F	G	H	I	J	K
1				count		sumproduct
2		red	red	OK		OK		row 1 could be: (blank), Red, Red = ok
3	red	red	red	OK		OK		row 2 could be: Red, Red, Red = ok
4	red	blue	blue	Difference		Difference		row 3 could be: Red, blue, blue = Diff
5			blue	OK		OK		row 4 could be: (blank), (blank), blue = ok
6
7
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=IF(COUNTA(A2:C2)-COUNTIF(A2:C2,LOOKUP(2,1/(A2:C2<>""),A2:C2))=0,"OK","Difference")
F2:F5	F2	=IF(SUMPRODUCT(--(A2:C2<> ""))-COUNTIF(A2:C2,LOOKUP(2,1/(A2:C2<>""),A2:C2))=0,"OK","Difference")

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[H3MSY]

i think this answers

Compare text values in mutiple columns, and ignore cells

Can someone please help provide a formula to comparing text values in multiple columns, for differences, and ignore blanks For example below: if the values (excluding blanks) in B,C,D,E match then F = "OK" if the values (excluding blanks) in B,C,D,E do not match then F = "Diffrerence" A B...

www.mrexcel.com

just modifying the range

Book2
	A	B	C	D	E	F	G
1
2		red	red	OK		row 1 could be: (blank), Red, Red = ok
3	red	red	red	OK		row 2 could be: Red, Red, Red = ok
4	red	blue	blue	Difference		row 3 could be: Red, blue, blue = Diff
5			blue	OK		row 4 could be: (blank), (blank), blue = ok
6
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=IF(COUNTA(A2:C2)-COUNTIF(A2:C2,LOOKUP(2,1/(A2:C2<>""),A2:C2))=0,"OK","Difference")

or from the link - sumproduct

Book2
	A	B	C	D	E	F	G	H	I	J	K
1				count		sumproduct
2		red	red	OK		OK		row 1 could be: (blank), Red, Red = ok
3	red	red	red	OK		OK		row 2 could be: Red, Red, Red = ok
4	red	blue	blue	Difference		Difference		row 3 could be: Red, blue, blue = Diff
5			blue	OK		OK		row 4 could be: (blank), (blank), blue = ok
6
7
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=IF(COUNTA(A2:C2)-COUNTIF(A2:C2,LOOKUP(2,1/(A2:C2<>""),A2:C2))=0,"OK","Difference")
F2:F5	F2	=IF(SUMPRODUCT(--(A2:C2<> ""))-COUNTIF(A2:C2,LOOKUP(2,1/(A2:C2<>""),A2:C2))=0,"OK","Difference")

etaf, many thanks this works perfectly. Thanks for taking the time out to reply. Have a great day

 

[H3MSY]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done!! (i think). Thanks for the advice Fluff

 

[etaf]

you are welcome

 

[Fluff]

As you have 365 another option is

=IF(COUNTA(UNIQUE(FILTER(A2:C2,A2:C2<>""),1))=1,"OK","Difference")