Grouping data according to totals

Assuming to work with the table below:

project name    total units
a               3
b               4  
c               1
d               5
e               2
f               5
g               8
h               12
i               8
j               10  
k               4
l               7 
m               9
n               19
o               15
p               6
q               3 


I would like the project names to be grouped with common units not exceeding for example 20. So if I add the project before f it gives me a total of 20. So this project group needs to be grouped and get a unique id from Excel ...

I want to easily determine which file number a particular project belongs to. So as soon as I enter the project name and common units, it can return me a number that indicates the file number that the project should go to.

project name    total units   file number
a               3             1
b               4             1
c               1             1
d               5             1
e               2             1
f               5             1 
g               8             2
h               12            2
i               8             3
j               10            3
k               4             4 
l               7             4
m               9             4
n               19            5
o               15            6
p               6             7
q               3             7 


The end result I would like to get, which sums common units and project names with an amount equal to or less than 20, are grouped and given a file number.

Is it possible for Excel to do such a thing?


source to share

3 answers

Don't use VBA, which you can easily do with Excel built-in functions. The function SUMIF()

will help a lot here.

Place the following formula in cell C2 (assuming the setting above)



The formula does the following:

  • If the name is "a" then check how many files are needed based on the units in "a"
  • For all other names: Sum the previous units in the current file (i.e. the file number in the cell above) and add the current project units. If the number is greater than 20, add 1 to the file number, otherwise use the same file number

I've tested this, but let me know if you have problems.



OK with the caveats mentioned by barryleajo and if your individual common units are between 1 and 19 then you need this algorithm, I think: -

If it the first line of data
    Running total=total units
    If (Previous running total + total units) > 20
        Running total=total units
        Running total=Previous running total + total units


So, in the table below, I have set D2 = B2 and E2 = 1,

then put the formula

= IF (D2 + B3> 20, B3, D2 + B3)

in D3


= IF (B3 = D3, E2 + 1, E2)

into E3 and pulled them out.

enter image description here



The following code works. I have added comments to help you understand the answer.

Dim total_units As Range
Dim file_number As Integer
Dim cumulative_sum As Integer

Sub filenumber()

    'Fill in column C header with string 'file_number'
    Range("C1") = "file_number"

    'Set total_units as the range variable
    Set total_units = ThisWorkbook.Sheets(1).Range("B2")

    'File_number starts equal to 1
    file_number = 1
    'Cumulative sum starts in the first row of total_units
    cumulative_sum = total_units

        'Loop until non empty rows of column project_name
        Do While Not total_units = ""

            'Fill in column C
            total_units.Offset(, 1) = file_number
            'Records the cumulative_sum in the row
            cumulative_sum = cumulative_sum + total_units.Offset(1, 0)

            'If cumulative sum exceeds 20, then, `file_number` changes and the start point in `cumulative_sum` also changes
            If cumulative_sum > 20 Then
            cumulative_sum = total_units.Offset(1, 0)
            file_number = file_number + 1
            End If

            'Move the range
            Set total_units = total_units.Offset(1, 0)

        'Next row

End Sub




All Articles