• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Programmatically group rows where accrued sum of column reaches just more than 1000 recursively

C

Coyote

New Member
Maybe the title sucks some, however, after reviewing this post I gained some hope for a problem that eludes me but didn't understand enough to adapt it

Sum row cells until value is reached

Hello, I would really appreciate if someone could help me with example attached. I need formula that sums up values in row (from column A to column G) while max sum (column I) is less or equal to max value (column H). Thank you, Petra
chandoo.org

So what I need help with is this.

A B
1 HDR1 HDR2
2 15
3 75
4 178
5 240
6 21
7 97

Given a column like column A, how to walk down the column starting at A2, happily summing all the way like one of the 7 dwarfs until the summed N-cells value pushes the sum over 1000. If possible, make those rows into a group or at worst, put a value into another column that can somehow be used to create a group in a later step. Then start over or continue to 2000, group, rinse and repeat every 1K until no more rows have a value that can be added to the summing process.

I sure hope that makes some sense cause with the way search terms are being returned by Google these days I am not finding a lot of help there.

If not, thanks for giving this a glance at least.
 
Hi
your title states " programmatically". Do you need a VBA answer. in this case this is the wrong subforum
 
As I said, I may have wrote a poor title. Is that is the only place an answer can be found? I posted here thinking that it wouldn't require it.
 
Coyote
One quick sample ...
with B1-cell You could set Your Group-value
Below B2-cell - You could see that group based B1-cell
Below C2-cell - You could fill Your values
Hint. Next time upload a sample Excel-file with expected results.
 

Attachments

I reasoned that a running subtotal in another column like this that acts upon only visible data, stuff not filtered out, would progress me to the next logical step of being able to identify breakpoints just over every 1000 that I could then use to break the data into groups but thats where my brain get stuck in a circular loop and I cannot progress.

I am trying to break rows up into batches so I can derive how many days it will take to re-tag a retail company due to insane cost increases due to COVID and merchandise availability. If 1000 items can be retagged each day and If I can break the data into batches, then I can answer that as well as provide lists from the same info to the staff and get the process started. If this was a one off problem I'd hack it out visually but I have over 700 vendors to work through so this would help a lot.

=IF(AGGREGATE(3,5,$AC2)=1,IF(AC2="","",SUBTOTAL(109,$AC$2:$AC2)))

Is there a path forward or is this beyond sane?
 
vletm said:
Coyote
One quick sample ...
with B1-cell You could set Your Group-value
Below B2-cell - You could see that group based B1-cell
Below C2-cell - You could fill Your values
Hint. Next time upload a sample Excel-file with expected results.
Click to expand...
I am looking at this now, sorry, it took me forever on my own post and didn't see this until I posted. Sorry for no sample. Its a monster file and I thought the question would be better than all of the eye rolling you'd have done had you seen what I was muching around in. Again, my bad, I am horrible at asking for help and worse when I do.
 
Last edited:
vletm said:
Coyote
One quick sample ...
with B1-cell You could set Your Group-value
Below B2-cell - You could see that group based B1-cell
Below C2-cell - You could fill Your values
Hint. Next time upload a sample Excel-file with expected results.
Click to expand...

I have attached a sample. Couple things:

Row 5 when hidden causes a change in column E but when I introduce your solution into column F (probably the wrong way) it does not change.

In your sample, I see where it works which made me smile a lot. It is possible!!! Kind of an I am Sparta moment there. Yet, I am working using a sheet that uses both static data and an ODBC connection to an Informix database along with VLOOKUP to mash the data together. I run into lots of situations where I have to work around the rigidity of excel in formatting and whatnot. I can't seem to adapt your solution to when filters are used or rows are hidden.

Do you have any insight on the sheet i have uploaded?
 

Attachments

Coyote
You've add other feature with Your text and with Your more realistic file.
... eg with filtering ... my sample works only if all rows are visible.
... that's why need a realistic sample file.
Could You accept VBA?
... instead of formulas
 
That is insane. It gets me over the goal line. May take me a while to understand it, however, a hearty thanks to you. This helps a lot.
 
You must log in or register to reply here.
Back
Top