Solved

Excel count backwards (sort of)

Posted on 2013-01-02
6
429 Views
Last Modified: 2013-01-02
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
Comment
Question by:hgj1357
6 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 38737893
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38737912
Can they overlap? In other words could A7 be, say, 4 so B5 through B7 would be 2 (and B4 be 1)?
0
 
LVL 2

Author Comment

by:hgj1357
ID: 38737933
There will be no overlaps.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 23

Expert Comment

by:NBVC
ID: 38737937
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
 
LVL 2

Author Closing Comment

by:hgj1357
ID: 38738001
Does what is says on the tin. Well played.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38738022
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Limit the # times a macro code will run 14 50
How to bypass VBA Project password 5 47
vba to flip column 14 17
V-Lookup 11 12
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question