Grouping Data by summing values and criteria

[jamesfell1984]

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)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD]Type[/TD]
[TD]City[/TD]
[TD]Mileage[/TD]
[TD]Population[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Data[/TD]
[TD]New York[/TD]
[TD]0-30[/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Data[/TD]
[TD]New York[/TD]
[TD]0-30[/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Data[/TD]
[TD]New York[/TD]
[TD]0-30[/TD]
[TD]225[/TD]
[/TR]
</tbody>[/TABLE]

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]

Maybe this in F2:

=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))))

 

[jamesfell1984]

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]

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

I look forward to hearing back.