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

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

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

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

=--(MATCH(TRUE,INDEX(A1:A$

copied down.

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

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