Grouping data according to total

Question

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?

Answer 1

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.