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

<tbody>
</tbody><colgroup><col span="5"><col></colgroup>


Thanks in advance for any help/tips!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,412
"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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
Office Version
  1. 365
Platform
  1. Windows
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.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,412
Try

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

GHARRISON

New Member
Joined
Nov 29, 2017
Messages
4

ADVERTISEMENT

"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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
Office Version
  1. 365
Platform
  1. Windows
Gharrison, did you try the solution I posted?
I think it should do what you are looking for.
 
Last edited:

GHARRISON

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

Forum statistics

Threads
1,140,928
Messages
5,703,220
Members
421,283
Latest member
MacroBegin

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
Top