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!

 

[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!

 

[Joe4]

You are welcome!

It actually isn't quite as confusing as it looks, except for that LOOKUP part.
Basically, we are just taking the count of non-blank entries and subtracting the number of entries equal to the last cell in that row with an entry.
If it is zero, it means the counts match (meaning all the entries are the same).
The COUNTA and COUNTIF functions are pretty straightforward. The part of the code that find that last non-empty entry in the row is the tricky part (I included a link that shows where that came from).

 

[Mani22]

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: How to return the first / last non blank cell in a row or column?).

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.

Hi, I have an exact requirement like the original question, however, I have a vlookup formula to return blank("") if no values from other tabs. So, the code considered the cells with formula as not blanks and returned "difference" for all. Is there any way I could considered only the cells that has values. Thanks.

 

[Joe4]

Welcome to the Board!

Hi, I have an exact requirement like the original question, however, I have a vlookup formula to return blank("") if no values from other tabs. So, the code considered the cells with formula as not blanks and returned "difference" for all. Is there any way I could considered only the cells that has values. Thanks.

See if this variation works:

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

 

[Mani22]

Welcome to the Board!



See if this variation works:

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

Thanks Rich. It worked

 

[Joe4]

Thanks Rich. It worked

You are welcome.
But who's Rich?

 

[vic_fox]

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: How to return the first / last non blank cell in a row or column?).

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.

Hi Joe4,

The Lookup function has a limit at 255 characters. Do you have a solution for that if you have text inputs with more than 255 characters?

Cheers!

 

[Joe4]

Hi Joe4,

The Lookup function has a limit at 255 characters. Do you have a solution for that if you have text inputs with more than 255 characters?

Cheers!

Nope.

Since the details of your question is different than the original one asked, it would be best for you to post it to a new thread, instead of posting to an existing old thread.
That way it will appear as a new question in the "Unanswered threads" listing and will get a lot more looks/attention (as many people looking to help go right to that list).