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

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

<tbody>
</tbody>

Table I need to create after VBA/Formula is applied

	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

<tbody>
</tbody>

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]

Worksheet Formulas Cell Formula B1 =IF(A1=1,1,IF(ISODD(COUNTIF(A$1:A1,1)),1,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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))