Randomly splitting a dynamic range into equal groups

Question

I have a dynamic range of values that I want to split into N groups, where N is specified by the user.

I want to do the following:

Split the group into N equal parts.
Specify which ones in the range that are to be in separate groups.
Have a "checker" to see if the math works out and it is possible to do (i.e. a group of 11 cannot be split into 2 groups).

Here is a scenario:

I have a list of 26 values (an array of letters, A to Z).
I want two groups, randomly split.
I specified 2 of the values where I want them to be separate (i.e. the letter B and X).

This should give me two groups, 13 values of "Group 1" and 13 values of "Group 2".

"Group 1" can contain something like ("B", "N", "V", "C", "T", ..... x 13)

"Group 2" contains ("X", "A", .... x 13)

The variables in this case are the # of values, # of groups to split, and specific values to split.

EDIT: google sheet example: https://docs.google.com/spreadsheets/d/1baZr8QAkFjw1UwsMOyphma6v_MNkWz-aCLx57yPsViE/edit#gid=1454808593

share a copy of your sheet with example of desired result

player0
– player0

2021-01-20 22:33:12 +00:00
Commented Jan 20, 2021 at 22:33 — player0
– player0, Commented Jan 20, 2021 at 22:33
@player0 just edited my question.

averageUsername123
– averageUsername123

2021-01-20 22:36:21 +00:00
Commented Jan 20, 2021 at 22:36 — averageUsername123
– averageUsername123, Commented Jan 20, 2021 at 22:36

player0 · Accepted Answer · 2021-01-21 00:50:26Z

2

try:

=ARRAYFORMULA(IF(B4<>TRUE,,TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(QUERY({
 ROUNDUP(SEQUENCE(COUNTA(B7:B))/(COUNTA(B7:B)/B2)), 
 SORT({RANDARRAY(COUNTA(B7:B)), FILTER(B7:B, B7:B<>"")})}, 
 "select max(Col3) group by Col2 pivot Col1"), "offset 1", 0),,9^9)), " "))))

UPDATE:

=ARRAYFORMULA(IFNA(CHAR(96+VLOOKUP(B7:B, {QUERY({B7:B, 
 COUNTIFS(C7:C, C7:C, ROW(C7:C), "<="&ROW(C7:C))*C7:C}, "where Col2<>0"); 
 {FILTER(B7:B, C7:C=FALSE, B7:B<>""), QUERY(SORT({RANDARRAY(SEQUENCE(B1-B2)), 
 ROUNDUP(SEQUENCE(B1-B2)/B3), QUERY({
 COUNTIFS(C7:C, C7:C, ROW(C7:C), "<="&ROW(C7:C))*C7:C, B7:B}, 
 "select Col2 where Col1=0 and Col2 is not null")}), "select Col2")}}, 2, 0))))

edited Jan 21, 2021 at 0:50

answered Jan 20, 2021 at 23:01

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

averageUsername123 Over a year ago

This is a great start, but not quite what I had in mind. 1) When I change the # of rewards (groups), it should be able to dynamically change. 2) When I change the # of play IDs (values), it should be able to dynamically change. 3) Instead of the groups being split in different columns, I want to see one column to label each of them in their groups beside them.

Collectives™ on Stack Overflow

Randomly splitting a dynamic range into equal groups

1 Answer 1

UPDATE:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related