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

hgj1357

Asked: 2013-01-02

Advertise Here

1 Solution

LVL 50

barry houdiniCommented: 2013-01-02

I assume there will be no overlaps?

Try this formula in B1 copied down

=IFERROR((MATCH(1,INDEX((A1:A$10000>0)+0,0),0)<=INDEX(A1:A$10000,MATCH(1,INDEX((A1:A$10000>0)+0,0),0)))+0,0)

regards, barry

LVL 52

Martin LissOlder than dirtCommented: 2013-01-02

Can they overlap? In other words could A7 be, say, 4 so B5 through B7 would be 2 (and B4 be 1)?

LVL 2

hgj1357Author Commented: 2013-01-02

There will be no overlaps.

LVL 23

NBVCCommented: 2013-01-02

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

LVL 2

hgj1357Author Commented: 2013-01-02

Does what is says on the tin. Well played.

LVL 52

Martin LissOlder than dirtCommented: 2013-01-02

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Expand your skills with this month's free Premium course.

Excel count backwards (sort of)

Join & Write a Comment Already a member? Login.

Featured Post