Compare text values in mutiple columns, and ignore cells

[GHARRISON]

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	C	D	E	F
Item 1	BE20			BE20	OK
Item 2	CZ28		BE20		Difference
Item 3	BE20	BE20	BE20	GB35	Difference
Item 4		CZ28			OK
Item 5	CZ28			CZ28	OK
Item 6	BE12				OK
Item 7	BE20	BE20	BE20		OK
Item 8	BE20	CZ28	CZ28	CZ28	Difference
Item 9	CZ28	CZ28			OK

Thanks in advance for any help/tips!

 

[Special-K99]

"if the values (excluding blanks) in B,C,D,E match"

What do you mean by this?
Do you mean if the values in B C D E F on the SAME row are the same (ignoring blanks) then "OK" else "Difference"

What result do you expect from this?

BE20 CZ28 CZ28 BE20

 

[Joe4]

Welcome to the Board!

What we can do is compare the count of the total number of entries in each row to the total number of entries matching the last non-blank entry in that row (see this for details on finding the last non-blank entry in a row: https://www.extendoffice.com/docume...rst-last-non-blank-cell-in-row-column.html#a1).

So, for row 2, the formula would look like:

=IF(COUNTA(B2:E2)-COUNTIF(B2:E2,LOOKUP(2,1/(B2:E2<>""),B2:E2))=0,"OK","Difference")

You can copy that formula down for all the other rows, and it should work. In my testing, it matches your expected results exactly.

 

[Special-K99]

Try

=IF((B1=C1)+(B1=D1)+(B1=E1)+1=COUNTA(B1:E1),"OK","Difference")
and copy down the column

 

[Joe4]

=IF((B1=C1)+(B1=D1)+(B1=E1)+1=COUNTA(B1:E1),"OK","Diff")
and copy down the column

That assumes that everything must be equal to column B. So, if column B does not have an entry, it won't work.
You can see that with example "Item 4". Your formula returns "Diff" instead of "OK".

 

[Special-K99]

Oh yep, well spotted.

 

[GHARRISON]

"if the values (excluding blanks) in B,C,D,E match"

What do you mean by this?
Do you mean if the values in B C D E F on the SAME row are the same (ignoring blanks) then "OK" else "Difference"

What result do you expect from this?

BE20 CZ28 CZ28 BE20

Correct - if the values in B, C , D, E on the same row are the same (ignoring blanks) then 'OK' else "Difference"

So if the any of the values are different... then "Difference"

BE20 CZ28 CZ28 BE20 = Difference

 

[Joe4]

Oh yep, well spotted.

My first line of thought was your solution as well. It would be so much easier if the first column is always populated!
Then you could just simplify my solution to:

=IF(COUNTA(B2:E2)-COUNTIF(B2:E2,B2)=0,"OK","Difference")

Alas, things are seldom made that easy, for us!

But the solution I posted just finds a cell that has a value to use in the COUNTIF, then everything works as it should.

 

[Joe4]

Gharrison, did you try the solution I posted?
I think it should do what you are looking for.

 

[GHARRISON]

Joe4 - Thank you so much, this works perfectly! I must admit, I don't fully understand the formula, but it works, and works on different ranges (number of columns)!

I struggled for hours earlier, but could not find a solution... so you have made my day - thanks!