CODEWITHSUNDEEP
vbaexcelexcel-formula
joeb
joeb

Reputation: 31

Grouping data according to total

Assuming that I have the table below to work with:

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 to have the project names grouped with the total units not exceeding 20 for example.
So if I add up project a up to f, it will give me a total of 20. So this group of projects to be grouped and given a unique identifier by Excel.

I want to easily determine which file number the specific project goes into. So as soon as I enter the project name and the total units, it can return a number to me saying which file number the project should go into. 

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 final outcome I would like to have whereby the total units are summed up and the project names with sum equal or less than 20 is grouped and given a file number.

Is it possible to have Excel do such thing?

Upvotes: 3

Views: 165

Answers (3)

Andre Silva
Andre Silva

Reputation: 4928

The following code works. I 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
        Loop

End Sub

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

Don't do with VBA, what you could easily do with Excel's inbuilt functions. The SUMIF() function will help a lot here

Place the following formula into cell C2 (assuming the setup above)

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

The formula is doing the following:

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

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

Upvotes: 3

Tom Sharpe
Tom Sharpe

Reputation: 34210

OK with the provisos mentioned by barryleajo and assuming your individual total units are between 1 and 19 you need this algorithm I think:-

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

So in the spreadsheet below I've set D2=B2 and E2=1,

then put the formula

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

into D3

and

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

into E3 and pulled them down.

enter image description here

Upvotes: 0

Related Questions