• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

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
0
hgj1357
Asked:
hgj1357
1 Solution
 
barry houdiniCommented:
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
0
 
Martin LissRetired ProgrammerCommented:
Can they overlap? In other words could A7 be, say, 4 so B5 through B7 would be 2 (and B4 be 1)?
0
 
hgj1357Author Commented:
There will be no overlaps.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
NBVCCommented:
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....
0
 
hgj1357Author Commented:
Does what is says on the tin. Well played.
0
 
Martin LissRetired ProgrammerCommented:
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

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now