Solved

Excel count backwards (sort of)

Posted on 2013-01-02
6
424 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel partial font macro 14 26
Hiding column macro 10 28
AutoFilter on a list of items.  Totalling each item in the list. 14 12
Merging-Splitting-Multiple-Rows 33 42
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now