# Grouping Data by summing values and criteria

#### jamesfell1984

##### New Member
Hi There,

I wonder if someone can help me?

I have a series of data (~700 rows, 5 columns) that I am looking to allocate into 5 groups using a random function and based on a series of rules / criteria that I would like to apply. I wondering if there is a simple formula that I can apply to the data series to help achieve this.

The data series is made up of 5 columns, these are:

• Column A - Number
• Column B - Type
• Column C - City
• Column E - Population

I am looking for a function that would allow me to create 5 groups, with the group identifier populated in column F, simply "Group 1"...."Group 2" etc would be fine, where:

• The population / sample is split as evenly as possible across the 5 groups based on the SUM of Column E.
• There is a rule that means that the same combination of Type, City and Radius only (Columns B, C and D) cannot appear within the same group. (e.g. scenarios such as the below would see the group allocation be different for each row)

 Column A Column B Column C Column D Column E Number Type City Mileage Population 1 Data New York 0-30 225 2 Data New York 0-30 225 1 Data New York 0-30 225

<tbody>
</tbody>

This sample data should you like to take a look can be found here.

Would anyone know how a formula that would allow me to do this quickly? I've been doing this manually to date and its a real bind.

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Nishant94

##### Well-known Member
Maybe this in F2:

Code:
``=IF(ROWS(F\$2:F2)=1,1,AGGREGATE(14,6,{1,2,3,4,5}/ISNA(MATCH({1,2,3,4,5},(B2&C2&D2=\$B\$1:B1&\$C\$1:C1&\$D\$1:D1)*\$F\$1:F1,0)),RANDBETWEEN(1,5-COUNTIFS(\$B\$1:B1,B2,\$C\$1:C1,C2,\$D\$1:D1,D2))))``

Last edited:

#### jamesfell1984

##### New Member
Thanks - I dropped this in and the sums for the groups came out as:

1 641,543
2 864,521
3 716,198
4 673,227
5 569,337

My target for each group is 692,965 - any idea how I could get the values above closer to this number Nishant?

#### jamesfell1984

##### New Member
Or if the group was larger, the sum of population divided by the number of groups.

I look forward to hearing back.

Replies
4
Views
315
Replies
0
Views
100
Replies
1
Views
98
Replies
4
Views
63
Replies
5
Views
177