Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel count backwards (sort of)
Posted on 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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
- +1
6 Comments
I assume there will be no overlaps?
Try this formula in B1 copied down
=IFERROR((MATCH(1,INDEX((A
regards, barry
Try this formula in B1 copied down
=IFERROR((MATCH(1,INDEX((A
regards, barry
Active today
Can they overlap? In other words could A7 be, say, 4 so B5 through B7 would be 2 (and B4 be 1)?
Active 7 days ago
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....
Active today
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
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 19 hours left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.