Create equal groups based on multiple criteria

[dizjackson]

I need to create 4 equal groups (a, b, c, and d) where they have an equal amount of passings (column N) per Hub (column AG). The groupings will consist of a list of nodes (column b). I know the groups won?t be exactly equal but I need them as close to equal as possible. The data in A through AE is the raw data and may change from time to time so ideally I?d be able to refresh and still keep the groups equal.



turf group screenshot.PNG

[Bernie Deitrick]

Post a file with your sample data and and example of what you expect. Just looking at your picture, Hub 5 has one node with 137 passings - unless the total passings for hub 5 is 4 times that (about 550), there would be no way to split those into 4 groups with "an equal amount of passings .... per Hub"

[dizjackson]

modifying sample workbook for upload

[Bernie Deitrick]

HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[dizjackson]

Sample attached. I'm thinking I'd need to determine the average number of passings for each hub then have a formula that adds the nodes to each group with the average for that hub being the max. In the actual data there will be 20 hubs and 1000s of nodes.

[Bernie Deitrick]

The attached has a macro that assigns groups, and formulas to extract them into the table. The macro is run by clicking the button at the top of column AL - it will work with however many data points you have.

[dizjackson]

Thank you!

When I copy and paste my data into this file and run the macro I get the error "run-time error '6': overflow".

What would be the function of the numbers in column 'AL'. There are no numbers in that column for the data that extends past the sample set.

I don't see anywhere in the Macro that references column AG. I'm wanting each group to have equal passings (column N) per hub (column AG).

Also, is there anyway to bring which group back each row ends up inm based on the macro, back into the report as an additional column - that would make it easier for filtering.

Updated sample attached