Excel count backwards (sort of)

I have the following column of numbers (column A) that may contain 10,000 + numbers. I need to be able to count backwards and insert 1s that add up to the number in column A. A9 contains a 5. I need B5 thru B9 to contain 1s.  Then A14 contains a 3, I need B12 thru B14 to contain 1s.

Can someone help me with this?

A      B
0      0
0      0
0      0
0      0
0      1
0      1
0      1
0      1
5      1
0      0
0      0
0      1
0      1
3      1
0      0
0      0
0      0
0      0
0      0
0      0
0      0
0      0
0      0
0      0
0      0
0      0
0      0
0      0
0      1
0      1
0      1
0      1
0      1
0      1
0      1
8      1
barry houdini

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can they overlap? In other words could A7 be, say, 4 so B5 through B7 would be 2 (and B4 be 1)?
hgj1357

There will be no overlaps.
I am not too sure if it will be too slow in processing for your, but here is a formula solution that might work...

=--(MATCH(TRUE,INDEX(A1:A\$10000>0,0),0)<=INDEX(A1:A\$10000,MATCH(TRUE,INDEX(A1:A\$10000>0,0),0)))

copied down.

Adjust ranges to minimum range size as possible....

Does what is says on the tin. Well played.
One or more of the submitted formulas will probably work but here's a macro you can use.

``````Sub CountBackward()
Dim lngLastRow As Long
Dim lngIndex As Long
Dim lngBack As Long

lngLastRow = Range("A65536").End(xlUp).Row

Application.ScreenUpdating = False

For lngIndex = lngLastRow To 1 Step -1
For lngBack = Cells(lngIndex, 1).Value To 1 Step -1
Cells(lngIndex, 2).Value = 1 'Cells(lngIndex, 2).Value + 1
lngIndex = lngIndex - 1
Next
Next
For lngIndex = 1 To lngLastRow
If Cells(lngIndex, 2).Value = "" Then
Cells(lngIndex, 2).Value = 0
End If
Next

Application.ScreenUpdating = True
End Sub``````