EXACT check ignore blanks

[shdwfx]

Hi all. I need a formula that will compare values across multiple columns (18 of them specifically) and return a TRUE if they are all the same or FALSE if not and I need it to ignore blanks. Help!?

[Tony Valko]

Will there ever be an instance when all of the cells are empty?

If so, what result would you expect?

[shdwfx]

Yes that's possible and the result should be a blank.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F
1	1		1		1	TRUE
2	2	1			2	FALSE
3
4	1	1	1	1	1	TRUE
5			1			TRUE
6	------	------	------	------	------	------

This array formula** entered in F1 and copied down:

=IF(COUNTA(A1:E1),COUNTIF(A1:E1,INDEX(A1:E1,MATCH(TRUE,A1:E1<>"",0)))=COUNTIF(A1:E1,"<>"),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[shdwfx]

BEAUTIFUL! Thank you so much!

[CAABYYC]

assume your source database is in A1:R10 (18 columns and 10 rows) with duplicated and blank.

U1= IF(COUNTIF(A1:R1,"")=18,"",SUM(IF(A1:R1<>"",1/COUNTIF(A1:R1,A1:R1)))=1) copy down

[CAABYYC]

sorry, my first formula has a error inside, after update, there is no difference between mine and tony's. both are NOT case sensitive.

[shdwfx]

Awesome thank you both!

[Tony Valko]

the difference between mine and tony's array formula is as follow
1. if all blank in same row,
Mine: FALSE
Tony: Blank (no indication)

They requested a blank cell. See posts 2 and 3.

2. case sensitive checking (example, A and a)
Mine: FALSE
Tony: True

My formula is not case sensitive.

[Tony Valko]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[Si-Phy]

This formula would appear to cover the issue that I have.

But, I have come up against an problem - I have populated the table using a VLOOKUP with an IFERROR, so where the cell is in theory blank, its populated with "" and so Excel takes the supposed blank into account

Thanks in advance.

[Pepe Le Mokko]

@Si-Phy

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread