Count consecutive 0’s and 1’s

[motilulla]

Using Excel 2010
Hello,

I have submitted example sheet below in the 20 columns C:V, I have table with 0’s and 1’s…I need a formula or VBA which can count consecutive 0’s and 1’s as shown in the columns Z:AS… if the count are correct column “AT” will sum 20...Please help.

Consecutive Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1
2
3
4			z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum
5			z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	Sum
6			1	0	0	0	1	0	1	0	1	1	0	0	1	0	1	1	0	0	1	0					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
7			1	1	1	0	1	1	0	0	1	0	1	1	0	0	1	0	1	1	0	0					3	1	2	2	1	1	2	2	1	1	2	2								20
8			1	0	0	0	1	0	1	0	1	1	0	0	1	0	1	1	0	0	1	0					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
9			1	0	0	1	1	0	1	0	0	1	1	0	0	0	0	1	1	0	0	0					1	2	2	1	1	2	2	4	2	3										20
10			0	1	0	1	0	0	1	0	0	1	1	1	0	0	0	1	1	1	0	0				1	1	1	1	2	1	2	3	3	3	2										20
11			1	1	1	1	1	1	0	0	1	0	0	0	0	1	1	0	0	0	0	1					6	2	1	4	2	4	1													20
12			1	0	0	1	0	0	0	1	0	0	1	0	1	0	0	0	1	0	1	0					1	2	1	3	1	2	1	1	1	3	1	1	1	1						20
13			0	1	1	1	0	1	0	0	0	1	0	0	1	0	0	1	0	0	1	0				1	3	1	1	3	1	2	1	2	1	2	1	1								20
14			0	0	0	1	1	1	1	0	1	1	0	1	1	1	1	1	0	1	1	1				3	4	1	2	1	5	1	3													20
15			0	1	1	0	0	0	0	0	1	0	0	1	1	0	1	0	0	1	1	0				1	2	5	1	2	2	1	1	2	2	1										20
16			1	0	1	1	0	1	1	1	1	0	0	0	1	1	1	0	0	0	1	1					1	1	2	1	4	3	3	3	2											20
17			0	0	1	0	1	0	1	0	0	0	1	0	1	0	0	0	1	0	1	0				2	1	1	1	1	1	3	1	1	1	3	1	1	1	1						20
18			0	0	0	0	0	1	0	0	0	1	1	1	0	0	0	1	1	1	0	0				5	1	3	3	3	3	2														20
19			1	0	0	1	1	0	0	0	0	1	0	0	1	1	0	1	0	0	1	1					1	2	2	4	1	2	2	1	1	2	2									20
Sheet1

Cell Formulas
Range	Formula
AT6:AT19	AT6	=SUM(Z6:AS6)

Regards,
Moti

 

[KRice]

Just getting back to looking at this. To make my offering work for 1's/2's....you need something in the helper formula to switch back and forth between those two values, so I've swapped out
--NOT(C6=1) for this instead...(C6=1)+1
So the helper formula becomes:

=IF(AND(ISNUMBER(B6),C6=B6),"",COUNTA(C6:INDEX(C6:$V6,,IFERROR(MATCH((C6=1)+1,C6:$V6,0)-1,COLUMNS(C6:$V6)))))

And then to position the final results correctly in the main results table, the two checks for $C6=0 become $C6=1, so the main formula is as follows:

MrExcel_20240301.xlsx
	Z	AA	AB	AC
4	Count	Count	Count	Count
5	0	1	0	1
6		1	3	1
7		3	1	2
8		1	3	1
Sheet5_01

Cell Formulas
Range	Formula
Z6:AC8	Z6	=IF(AND(COLUMNS($Z:Z)=1,$C6=1),"",IFERROR(INDEX($AV6:$BO6,,AGGREGATE(15,6,COLUMN($A:$T)/($AV6:$BO6<>""),COLUMNS($Z:Z)-($C6=1))),""))

 

[motilulla]

KRice, thank you, following your instruction I changed formula what I am doing wrong not getting expected results. Please can you revise it?

Consecutive Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP
1
2
3																																																1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2
4			z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum		Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum
5			z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	Sum		0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	Sum
6			2	1	1	1	2	1	2	1	2	2	1	1	2	1	2	2	1	1	2	1				1	19	1	15	1	13	2	10	1	7	2	4	1	1							78		1	19			1	15	1	13	2		10		1	7	2		4		1	1	78
7			2	2	2	1	2	2	1	1	2	1	2	2	1	1	2	1	2	2	1	1				3	17	2	14	1	11	2	8	1	5	2	2									68		3			17	2		14		1	11	2		8		1	5	2		2		68
8			2	1	1	1	2	1	2	1	2	2	1	1	2	1	2	2	1	1	2	1				1	19	1	15	1	13	2	10	1	7	2	4	1	1							78		1	19			1	15	1	13	2		10		1	7	2		4		1	1	78
Sheet1

Cell Formulas
Range	Formula
Z6:AS8	Z6	=IF(AND(COLUMNS($Z:Z)=1,$C6=1),"",IFERROR(INDEX($AV6:$BO6,,AGGREGATE(15,6,COLUMN($A:$T)/($AV6:$BO6<>""),COLUMNS($Z:Z)-($C6=1))),""))
BP6:BP8,AT6:AT8	AT6	=SUM(Z6:AS6)
AV6:BO8	AV6	=IF(AND(ISNUMBER(B6),C6=B6),"",COUNTA(C6:INDEX(C6:$V6,,IFERROR(MATCH(--NOT(C6=1),C6:$V6,0)-1,COLUMNS(C6:$V6)))))

Kind regards,
Moti

 

[KRice]

Thanks for asking...and sorry, it looks as if I made a mistake when I edited the formula to work with 1's and 2's. The final results formula should use a $C6=2 in two places, rather than $C6=1 that I mentioned previously. And in the helper table, it looks like you did not revise the formula as I described previously. One note about the helper table, I would not label its columns with 1's and 2's or 0's and 1's, as the helper table only counts the number of adjacent cells with matching values, and then as it moves along the row, it counts the number of adjacent cells with the alternating value. But the helper table results do not align correctly with a 0/1 or 1/2 column. The alignment is done in the final formula...the main results table.

MrExcel_20240301.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP
4	z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum																						Sum
5	z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	Sum		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	Sum
6	2	1	1	1	2	1	2	1	2	2	1	1	2	1	2	2	1	1	2	1					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20		1	3			1	1	1	1	2		2		1	1	2		2		1	1	20
7	2	2	2	1	2	2	1	1	2	1	2	2	1	1	2	1	2	2	1	1					3	1	2	2	1	1	2	2	1	1	2	2								20		3			1	2		2		1	1	2		2		1	1	2		2		20
8	2	1	1	1	2	1	2	1	2	2	1	1	2	1	2	2	1	1	2	1					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20		1	3			1	1	1	1	2		2		1	1	2		2		1	1	20
Sheet9

Cell Formulas
Range	Formula
Z6:AS8	Z6	=IF(AND(COLUMNS($Z:Z)=1,$C6=2),"",IFERROR(INDEX($AV6:$BO6,,AGGREGATE(15,6,COLUMN($A:$T)/($AV6:$BO6<>""),COLUMNS($Z:Z)-($C6=2))),""))
AT6:AT8,BP6:BP8	AT6	=SUM(Z6:AS6)
AV6:BO8	AV6	=IF(AND(ISNUMBER(B6),C6=B6),"",COUNTA(C6:INDEX(C6:$V6,,IFERROR(MATCH((C6=1)+1,C6:$V6,0)-1,COLUMNS(C6:$V6)))))

 

[motilulla]

Thanks for asking...and sorry, it looks as if I made a mistake when I edited the formula to work with 1's and 2's. The final results formula should use a $C6=2 in two places, rather than $C6=1 that I mentioned previously. And in the helper table, it looks like you did not revise the formula as I described previously. One note about the helper table, I would not label its columns with 1's and 2's or 0's and 1's, as the helper table only counts the number of adjacent cells with matching values, and then as it moves along the row, it counts the number of adjacent cells with the alternating value. But the helper table results do not align correctly with a 0/1 or 1/2 column. The alignment is done in the final formula...the main results table.

MrExcel_20240301.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP
4	z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum																						Sum
5	z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	Sum		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	Sum
6	2	1	1	1	2	1	2	1	2	2	1	1	2	1	2	2	1	1	2	1					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20		1	3			1	1	1	1	2		2		1	1	2		2		1	1	20
7	2	2	2	1	2	2	1	1	2	1	2	2	1	1	2	1	2	2	1	1					3	1	2	2	1	1	2	2	1	1	2	2								20		3			1	2		2		1	1	2		2		1	1	2		2		20
8	2	1	1	1	2	1	2	1	2	2	1	1	2	1	2	2	1	1	2	1					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20		1	3			1	1	1	1	2		2		1	1	2		2		1	1	20
Sheet9

Cell Formulas
Range	Formula
Z6:AS8	Z6	=IF(AND(COLUMNS($Z:Z)=1,$C6=2),"",IFERROR(INDEX($AV6:$BO6,,AGGREGATE(15,6,COLUMN($A:$T)/($AV6:$BO6<>""),COLUMNS($Z:Z)-($C6=2))),""))
AT6:AT8,BP6:BP8	AT6	=SUM(Z6:AS6)
AV6:BO8	AV6	=IF(AND(ISNUMBER(B6),C6=B6),"",COUNTA(C6:INDEX(C6:$V6,,IFERROR(MATCH((C6=1)+1,C6:$V6,0)-1,COLUMNS(C6:$V6)))))

KRice, I appreciate your time and help. All worked perfect as I wanted, thanks for about the helper column note that helps.

Have a beautiful weekend and good luck.

Kind regards,
Moti

 

[KRice]

You’re welcome…happy to help.