How do I assign evenly the Group # using a multi-row formula.

[DavidP07]

Hi, I would like to organize my data so I can see the group # spread evenly using the Multi-row formula.

[Prometheus]

@DavidP07 It would be super helpful if you could upload some dummy data. It's much easier to build a solution with actual data than rebuild it from what we could see in a picture.

[DavidP07]

Sure here is a sample csv file for the problem I have worked, thanks a lot.

[fluteman · Accepted solution]

To spread people into groups evenly... or the best you can...

You don't necessarily need to use a multi row formula. you can figure out what constant you want to divide the population into and then use a combination of the RecordID tool and the Formula tool with a ceiling function.

CEIL([RecordID] / Constant)

I used a constant of 3.

(note: i went down the rabbit hole of looking for a remainder and calculating the number of potential groups - but the ceiling formula is sufficient)

[caltang · Accepted solution]

Why not use the Tile tool and select unique grouping? Then select your group (field) of choice? 

The tile number gets you unique values. Any duplicates will get you the same number, but the tile sequence number that comes along with it helps tell you the difference.

[DavidP07]

Hi, I'm sorry for my late response, I was trying to work the doc but still, I think I struggled with arranging the data evenly by 20 equal groups for each attendant.

[caltang · Accepted solution]

Since there are no further conditions other than to push the 261 ungrouped people in groups, please find attached.

The core idea is that you have 500 people, and 25 groups have been made already. Why not push the remaining 261 ungrouped people into the 25 groups? All you have to do is find the remainder of each group that has people, how many people it takes to hit 20 per group.

From there, the match checks out. Try the above @DavidP07 

If you want to randomize the group, you can add a RAND() element in formula tool above and sort the RAND() and give it a Record ID to attach to the RowCount. Be careful though, each runs means a new iteration of ungrouped people in different groups. So do with it as you please.

Hope this helps - if it does, kindly leave a like and mark as accepted solution to close the thread.

Thanks!