Thanks:  0

# Thread: Compare text values in mutiple columns, and ignore cells

1. ## 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 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!

2. ## Re: Compare text values in mutiple columns, and ignore cells

"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

3. ## Re: Compare text values in mutiple columns, and ignore cells

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/documen...column.html#a1).

So, for row 2, the formula would look like:
Code:
`=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.

4. ## Re: Compare text values in mutiple columns, and ignore cells

Try

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

5. ## Re: Compare text values in mutiple columns, and ignore cells

=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".

6. ## Re: Compare text values in mutiple columns, and ignore cells

Oh yep, well spotted.

7. ## Re: Compare text values in mutiple columns, and ignore cells

Originally Posted by 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

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

8. ## Re: Compare text values in mutiple columns, and ignore cells

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:
Code:
`=IF(COUNTA(B2:E2)-COUNTIF(B2:E2,B2)=0,"OK","Difference")`
Alas, things are seldom made that easy, four 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.

9. ## Re: Compare text values in mutiple columns, and ignore cells

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

10. ## Re: Compare text values in mutiple columns, and ignore cells

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!