Compare text values in mutiple columns, and ignore cells

GHARRISON

New Member
Joined
Nov 29, 2017
Messages
4
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"

ABCDEF
Item 1BE20BE20OK
Item 2CZ28BE20Difference
Item 3BE20BE20BE20GB35Difference
Item 4CZ28OK
Item 5CZ28CZ28OK
Item 6BE12OK
Item 7BE20BE20BE20OK
Item 8BE20CZ28CZ28CZ28Difference
Item 9CZ28CZ28OK

Thanks in advance for any help/tips!
 
Last edited by a moderator:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
"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
 
Upvote 0
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:
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.
 
Upvote 1
Solution
Try

=IF((B1=C1)+(B1=D1)+(B1=E1)+1=COUNTA(B1:E1),"OK","Difference")
and copy down the column
 
Last edited:
Upvote 0
=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".
 
Upvote 0
"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
 
Upvote 0
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, 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.
 
Last edited:
Upvote 0
Gharrison, did you try the solution I posted?
I think it should do what you are looking for.
 
Last edited:
Upvote 0
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!:)
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top