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
Microsoft OfficeMicrosoft Excel
Last Comment
Martin Liss
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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$
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