Grouping Data by summing values and criteria

jamesfell1984

New Member
Joined
Sep 4, 2018
Messages
3
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 D - Radius
  • 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 AColumn BColumn CColumn DColumn E
NumberTypeCityMileagePopulation
1DataNew York0-30225
2DataNew York0-30225
1DataNew York0-30225

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

Nishant94

Well-known Member
Joined
May 8, 2015
Messages
507
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
Joined
Sep 4, 2018
Messages
3
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
Joined
Sep 4, 2018
Messages
3
Or if the group was larger, the sum of population divided by the number of groups.

I look forward to hearing back.
 

Forum statistics

Threads
1,082,548
Messages
5,366,227
Members
400,880
Latest member
dwb

Some videos you may like

This Week's Hot Topics

Top