Cell contains only 1 value

[Benong]

Hi,
How to check if a cell contains the same value (at least 3 values) on the same row across multiple columns?
Col A. Col B. Col C Col D
Car. Plane. Kettle Dog
1,1,1,1 0,0,0,0,0,0 1,0,11,0,0 1,1

Ans: Car, Plane (return values)

 

[Benong]

Col A
Car
1,1,1,1

Col B
Plane
0,0,0,0,0,0

Col C
Kettle
1,1,1,1 0,1,0,11,0,0

Col D
Dog
1,1

 

[Benong]

Using cell formula only, no VBA, …

 

[Benong]

The values inside a cell are either 0 or 1 only

 

[Cubist]

It's unclear on your data structure. Like this?

Book1
	A	B	C	D
1	Car	Plane	Kettle	Dog
2	1	0	1	1
3	1	0	1	1
4	1	0	1
5	1	0	1
6		0	0
7			1
8			0
9			1
10			1
11			0
12			0
Sheet5

 

[Eric W]

Or is it like this:

Book1
	A	B	C	D	E	F	G
1	Car	Plane	Kettle	Dog		Car	Plane
2	1,1,1,1	0,0,0,0,0	1,1,1,1,0,1,0,1,1,0,0	1,1
Sheet1

Cell Formulas
Range	Formula
F1:G1	F1	=FILTER(A1:D1,(ISNUMBER(FIND("1,1,1",A2:D2)))*ISERROR((FIND("0",A2:D2)))+(ISNUMBER(FIND("0,0,0",A2:D2))*ISERROR(FIND("1",A2:D2))))
Dynamic array formulas.

 

[Benong]

Or is it like this:

Book1
	A	B	C	D	E	F	G
1	Car	Plane	Kettle	Dog		Car	Plane
2	1,1,1,1	0,0,0,0,0	1,1,1,1,0,1,0,1,1,0,0	1,1
Sheet1

Cell Formulas
Range	Formula
F1:G1	F1	=FILTER(A1:D1,(ISNUMBER(FIND("1,1,1",A2:D2)))*ISERROR((FIND("0",A2:D2)))+(ISNUMBER(FIND("0,0,0",A2:D2))*ISERROR(FIND("1",A2:D2))))
Dynamic array formulas.

This is the structure. It works on one row only. can expand formula to include more rows?

 

[Benong]

This is the structure. It works on one row only. can expand formula to include more rows?

Many thanks for your response.

 

[Peter_SSs]

Assuming only 0s and 1s like your sample, or at least only single-digit values, possibly this formula in F2, copied down?

25 02 24.xlsm
	A	B	C	D	E	F	G	H	I
1	Car	Plane	Kettle	Dog
2	1,1,1,1	0,0,0,0,0	1,1,1,1,0,1,0,1,1,0,0	1,1		Car	Plane
3	0,0	0,0	0,0	0,0
4
5	0,0,0,1	0,0,0	1,1,1,1,1,1	0,0,0,0		Plane	Kettle	Dog
6	0,0,0	0,0,0	0,0,0	0,0,0		Car	Plane	Kettle	Dog
7	0,0,0,1	0,0,0,1	1,1,1,1,1,1	0,0,0,1		Kettle
Same value

Cell Formulas
Range	Formula
F2:G2,F6:I6,F5:H5,F3:F4,F7	F2	=FILTER(A$1:D$1,(SUBSTITUTE(A2:D2&",",LEFT(A2:D2,2),"")="")*(LEN(A2:D2)>4),"")
Dynamic array formulas.

 

[Benong]

Many many thanks for your time & effort