Group range of cells based on sum that is closest or equal to specific max value

Question

I have a range of cells in a row whose text I want to Join() together. The number of total characters in the final "joined cell" is the constraint, that is why I need to group the cells and create multiple joined cells. I want to dynamically calculate cell ranges that are closest or equal to that max. The sequence of cells is fixed, so a group of cells always needs to be together.

I have outlined my desired outcome here:

I probably need some helper columns, but I have a tough time figuring out the best approach.

Answer 1

I would use 2 helper rows.

First one will calculate the cumulative sum per group, with =A2 in the first cell and subsequent cells having:

=IF(B1+A3>$C6,B1,A3+B1)

Second one will give each group a number, the first cell being 1 and subsequent ones having:

=IF(B3>A3,A4,A4+1)

Both the above should be copied until at least one cell more than the original table so the below formulae can 'see' where they should stop.

Then add the numbers for each groups next to the small table that you have, and use some formulae. The first formula gives the address in text. The second one uses a range to join text:

=SUBSTITUTE(CELL("address",OFFSET(A$2,0,MATCH(C8,$4:$4,0)-1))&":"&CELL("address",OFFSET(A$2,0,MATCH(C8+1,$4:$4,0)-2)),"$","")

=TEXTJOIN("-",0,OFFSET(A$2,0,MATCH(C8,$4:$4,0)-1,1,MATCH(C8+1,$4:$4,0)-MATCH(C8,$4:$4,0)))

gSpreadsheet sample

Answer 2

Can you do it without helper rows?

Yes, in Google Sheets you can use Sumif to create an array of running totals and therefore you can get the subtotal for the first group of columns by checking if the running total is less than or equal to 10. Then for subsequent groups you can subtract the sum of all previous groups (which admittedly does require a helper cell) and repeat the process.

Once you've got the subtotals, you can use a similar method to join the text cells together.

To get the subtotals, in B6:

=ArrayFormula(max(if((sumif(column(A$1:O$1),"<="&column(A$1:O$1),A$1:O$1)-sum(B$5:B5))<=10,sumif(column(A$1:O$1),"<="&COLUMN(A$1:O$1),A$1:O$1)-sum(B$5:B5))))

Then to join the strings, in C6:

=ArrayFormula(textjoin(",",true,if((sumif(column(A$1:O$1),"<="&column(A$1:O$1),A$1:O$1)-sum(B$5:B5)<=10)*(sumif(column(A$1:O$1),"<="&column(A$1:O$1),A$1:O$1)-sum(B$5:B5)>0),A$2:O$2,"")))