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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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:
Upvote 0
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?
 
Upvote 0
Or if the group was larger, the sum of population divided by the number of groups.

I look forward to hearing back.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top