in-filling a column with 1's & 0's

[hissonrr]

Good day all,

I was having some trouble and wasn't sure if i needed a VBA solution or could actually achieve the result I want with a formula of some kind.

basically i have a column of 1s and 0s like column A in the table below. What I need to happen is to create a new Column B that recognized the first instance a 1 occurs and then infill it to the next instance a 1 occurs, then continue on doing it multiple times down the column.

For example...

Initial Table

[TABLE="width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Table I need to create after VBA/Formula is applied

[TABLE="width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]



Any help would be greatly appreciated.

-R

 

[jtakw]

Hi,

This is one way to do it with a formula:

Book1
	A	B
1	0	0
2	0	0
3	1	1
4	0	1
5	0	1
6	1	1
7	0	0
8	0	0
9	0	0
10	1	1
11	0	1
12	1	1
13	0	0
Sheet122

Cell Formulas
Range	Formula
B1	=IF(A1=1,1,IF(ISODD(COUNTIF(A$1:A1,1)),1,0))

B1 formula copied down.

 

[Rick Rothstein]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(A1=1,1,IF(ISODD(COUNTIF(A$1:A1,1)),1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

You can shorten that formula slightly...

=IF(A1=1,1,0+ISODD(COUNTIF(A$1:A1,1)))

 

[Tetra201]

Shortening further:

=--OR(A1,ISODD(COUNTIF(A$1:A1,1)))

 

[Rick Rothstein]

Shortening further:

=--OR(A1,ISODD(COUNTIF(A$1:A1,1)))

I was just about to post that same formula when I saw you had beaten me to it.

 

[Tetra201]

@ Rick: This does not happen often

 

[hissonrr]

Thanks a lot guys I didn't even know the ISODD function existed.

i appreciate it consider this solved.

 

[jtakw]

You're welcome, glad to help.

 

[shg]

Or

=IF(A1=1,1,MOD(SUM(A$1:A1),2))