Adjacent Non Blank Cells

[errollflynn]

Hello,

I have the data set below and am looking for a formula that will identify instances where there are six or more non blank, adjacent cells in a row. I've tried various forms of using counta with no success.

Thank you in advance.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Sum of Shift	Months
2	Row Labels	1	2	3	4	5	6	7	8	9	10	11	12
3	Person A	8		25		28	25	26	28	23	30	28
4	Person B	23	20		21		22	18		21	21		22
5	Person C	12	22	27	25	24	21		12	18	30	23	19
6	Person D	7	14	25		28	22	14	16	23	26		15
7	Person E	3	18		26	25		10	11		26	18	18
Sheet1

 

[Anonymous1378]

Try this?

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sum of Shift	Months
2	Row Labels	1	2	3	4	5	6	7	8	9	10	11	12
3	Person A	8		25		28	25	26	28	23	30	28		TRUE
4	Person B	23	20		21		22	18		21	21		22	FALSE
5	Person C	12	22	27	25	24	21		12	18	30	23	19	TRUE
6	Person D	7	14	25		28	22	14	16	23	26		15	TRUE
7	Person E	3	18		26	25		10	11		26	18	18	FALSE
Sheet1

Cell Formulas
Range	Formula
N3:N7	N3	=OR(FREQUENCY(IF(B3:M3<>"",COLUMN(B3:M3),0),IF(B3:M3="",COLUMN(B3:M3),0))>=6)

 

[errollflynn]

Try this?

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sum of Shift	Months
2	Row Labels	1	2	3	4	5	6	7	8	9	10	11	12
3	Person A	8		25		28	25	26	28	23	30	28		TRUE
4	Person B	23	20		21		22	18		21	21		22	FALSE
5	Person C	12	22	27	25	24	21		12	18	30	23	19	TRUE
6	Person D	7	14	25		28	22	14	16	23	26		15	TRUE
7	Person E	3	18		26	25		10	11		26	18	18	FALSE
Sheet1

Cell Formulas
Range	Formula
N3:N7	N3	=OR(FREQUENCY(IF(B3:M3<>"",COLUMN(B3:M3),0),IF(B3:M3="",COLUMN(B3:M3),0))>=6)

Hello,

Your solution is trending in the right direction but it seems to deliver a false positive in some instances. Please see the third line below.

aeg4_caps_payroll_report_2022-01-01_thru_2022-12-31.csv
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
186		5	3	5	3	2	3			8	6	7	42	9	TRUE
187	12	12	7	2	2	2						5	42	7	TRUE
188	10	9	16	6									41	4	TRUE
189	1	1	6	7	8	4	1	1	2	4	3	2	40	12	TRUE
Sheet2

Cell Formulas
Range	Formula
O186:O189	O186	=COUNTA(B186:M186)
P186:P189	P186	=OR(FREQUENCY(IF(B186:M186<>"",COLUMN(B186:M186),0),IF(B186:M186="",COLUMN(B186:M186),0))>=6)

 

[Anonymous1378]

Hello,

Your solution is trending in the right direction but it seems to deliver a false positive in some instances. Please see the third line below.

aeg4_caps_payroll_report_2022-01-01_thru_2022-12-31.csv
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
186		5	3	5	3	2	3			8	6	7	42	9	TRUE
187	12	12	7	2	2	2						5	42	7	TRUE
188	10	9	16	6									41	4	TRUE
189	1	1	6	7	8	4	1	1	2	4	3	2	40	12	TRUE
Sheet2

Cell Formulas
Range	Formula
O186:O189	O186	=COUNTA(B186:M186)
P186:P189	P186	=OR(FREQUENCY(IF(B186:M186<>"",COLUMN(B186:M186),0),IF(B186:M186="",COLUMN(B186:M186),0))>=6)

Good point, change the zeroes to ""

Book7
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
186		5	3	5	3	2	3			8	6	7	42	9	TRUE
187	12	12	7	2	2	2						5	42	7	TRUE
188	10	9	16	6									41	4	FALSE
189	1	1	6	7	8	4	1	1	2	4	3	2	40	12	TRUE
Sheet1

Cell Formulas
Range	Formula
O186:O189	O186	=COUNTA(B186:M186)
P186:P189	P186	=OR(FREQUENCY(IF(B186:M186<>"",COLUMN(B186:M186),""),IF(B186:M186="",COLUMN(B186:M186),""))>=6)

 

[errollflynn]

Good point, change the zeroes to ""

Book7
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
186		5	3	5	3	2	3			8	6	7	42	9	TRUE
187	12	12	7	2	2	2						5	42	7	TRUE
188	10	9	16	6									41	4	FALSE
189	1	1	6	7	8	4	1	1	2	4	3	2	40	12	TRUE
Sheet1

Cell Formulas
Range	Formula
O186:O189	O186	=COUNTA(B186:M186)
P186:P189	P186	=OR(FREQUENCY(IF(B186:M186<>"",COLUMN(B186:M186),""),IF(B186:M186="",COLUMN(B186:M186),""))>=6)

THANK YOU!

 

[Peter_SSs]

Another simple option to consider

23 08 14.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	8		25		28	25	26	28	23	30	28		TRUE
4	23	20		21		22	18		21	21		22	FALSE
5	12	22	27	25	24	21		12	18	30	23	19	TRUE
6	7	14	25		28	22	14	16	23	26		15	TRUE
7	3	18		26	25		10	11		26	18	18	FALSE
8		5	3	5	3	2	3			8	6	7	TRUE
9	12	12	7	2	2	2						5	TRUE
10	10	9	16	6									FALSE
11	1	1	6	7	8	4	1	1	2	4	3	2	TRUE
6 or more

Cell Formulas
Range	Formula
N3:N11	N3	=ISNUMBER(FIND(111111,CONCAT(IF(B3:M3="",2,1))))

 

[Anonymous1378]

That solution is certainly more compact; the real advantage my solution offers would be to find the maximum continuous adjacent cells, but that does not seem to be needed here.

 

[Herakles]

That solution is certainly more compact; the real advantage my solution offers would be to find the maximum continuous adjacent cells, but that does not seem to be needed here.

Just wait for the project creep.

 

[Peter_SSs]

the real advantage my solution offers would be to find the maximum continuous adjacent cells,

I would still use the same basic approach - either column O or P depending on the requirement.

23 08 14.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M		O	P
2														Max consec	Max consec >=6
3	8		25		28	25	26	28	23	30	28			7	7
4	23	20		21		22	18		21	21		22		2
5	12	22	27	25	24	21		12	18	30	23	19		6	6
6	7	14	25		28	22	14	16	23	26		15		6	6
7	3	18		26	25		10	11		26	18	18		3
8		5	3	5	3	2	3			8	6	7		6	6
9	12	12	7	2	2	2						5		6	6
10	10	9	16	6										4
11	1	1	6	7	8	4	1	1	2	4	3	2		12	12
6 or more

Cell Formulas
Range	Formula
O3:O11	O3	=MAX(LEN(TEXTSPLIT(CONCAT(IF(B3:M3="",2,1)),2)))
P3:P11	P3	=LET(m,MAX(LEN(TEXTSPLIT(CONCAT(IF(B3:M3="",2,1)),2))),IF(m>5,m,""))

 

[errollflynn]

Another simple option to consider

23 08 14.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	8		25		28	25	26	28	23	30	28		TRUE
4	23	20		21		22	18		21	21		22	FALSE
5	12	22	27	25	24	21		12	18	30	23	19	TRUE
6	7	14	25		28	22	14	16	23	26		15	TRUE
7	3	18		26	25		10	11		26	18	18	FALSE
8		5	3	5	3	2	3			8	6	7	TRUE
9	12	12	7	2	2	2						5	TRUE
10	10	9	16	6									FALSE
11	1	1	6	7	8	4	1	1	2	4	3	2	TRUE
6 or more

Cell Formulas
Range	Formula
N3:N11	N3	=ISNUMBER(FIND(111111,CONCAT(IF(B3:M3="",2,1))))

Another fascinating solution! Thank you!