CODEWITHSUNDEEP
excelexcel-formulavba
Kenny164
Kenny164

Reputation: 53

How can I group a column of totals into a set where each set has a maximum

I have a collection of records, each record has a total; I want to split the whole collection up into sets, each set can only hold so much (20 in the example).

I have thought about two methods so far and I've shown a simple representation below:

Sets are separate:

Another Idea I toyed with is just to add a set column onto the source data set:

I'm trying to figure out an easy way to complete this using formula, if it's easier to complete in VBA I'll then go that route.

Thanks for the help, I've been thinking about this all week and don't even know what to search for.

**Edit: I'm an idiot.

Upvotes: 1

Views: 59

Answers (4)

Dattel Klauber
Dattel Klauber

Reputation: 833

The described problem can also be solved with a dynamic array solution in newer versions of excel (Office365).

An example for a possible solution could look like this:

screenshot_1

which uses the following formula for the sets 2-5:

=LET(
    rows,$B$5:$B$17,
    vals,$C$5:$C$17,
    excl,$F$5:F$15,
    rows_filt,OFFSET(rows,COUNT(excl),0,COUNT(rows)-COUNT(excl)),
    vals_filt,OFFSET(vals,COUNT(excl),0,COUNT(rows)-COUNT(excl)),
    cumsum,MMULT(N(ROW(vals_filt)>=TRANSPOSE(ROW(vals_filt))),vals_filt),
    FILTER(rows_filt,cumsum<=20)
)

but to avoid reference to itself, the formula for set 1 is:

=LET(
    rows,$B$5:$B$17,
    vals,$C$5:$C$17,
    cumsum,MMULT(N(ROW(vals)>=TRANSPOSE(ROW(vals))),vals),
    FILTER(rows,cumsum<=20)
)

(also shown in the screenshot here)

Upvotes: 0

user3819867
user3819867

Reputation: 1120

=ROUNDUP(SUM(R5C[-1]:RC[-1])/20,0)
or
=ROUNDUP(SUM(C$5:C5)/20,0) where the formula starts at D5 and is pulled down.

Upvotes: 0

Kenny164
Kenny164

Reputation: 53

I found the answer by browsing through the related section.

=IF(A2="a",ROUNDDOWN((B2-1)/20,0)+1,IF(SUMIF($C1:C$2,C1,$B1:B$2)+B2>20,C1+1,C1))

Here is a link to that answer.

I'm going to accept @sous2817's answer because it was the most helpful and would've been the path I took if I went for the VBA approach.

Thanks again guys!

Upvotes: 0

sous2817
sous2817

Reputation: 3960

I couldn't come up with a formula, but here is a pretty brute force way of doing it:

Sub test()

Dim i           As Long, _
    lr          As Long, _
    sumValue    As Long, _
    counter     As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
counter = 1
For i = 1 To lr
    sumValue = sumValue + Range("A" & i)
        If sumValue <= 20 Then
            Range("B" & i).Value = counter
        Else
            sumValue = 0
            counter = counter + 1
            i = i - 1
        End If
Next i
End Sub

For what it's worth, my results were a tad bit different as I calculated 5+10+12 to be greater than 20...

Upvotes: 1

Related Questions