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
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)
=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 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.
source to share
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
Else
If (Previous running total + total units) > 20
Running total=total units
Else
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
and
= IF (B3 = D3, E2 + 1, E2)
into E3 and pulled them out.
source to share
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
Loop
End Sub
source to share