# 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?

+3

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.

+3

source

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.

0

source

``````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
```

```
0

source

All Articles