Link to home
Start Free TrialLog in
Avatar of hgj1357
hgj1357

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

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

ASKER

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....
Avatar of hgj1357

ASKER

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

Open in new window