How would I take apart a comma list randomize it them put the list back together in smaller groups

Question

I'm attempting to make a excel (ideally google sheets or excel) but I'll figure that one out if I need to. For format reasons I need to Keep this to 2 rows but can use as many columns as I need. The Third row was just for troubleshooting. Basically I need to be able to put a comma separated list into cell A2 and and tell it how many groups I need the names separated into, the List than needs to be randomized and sorted into the number of groups (up to 50 groups).

per ti7's request please find the following explanation and examples

I got excel to count the number on people in the list in a2 using the following formula in cell b2:

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

This works as intended.

From there cell c2 is a hard value of the number of groups needed.

D2 divides the number number of people from the number of groups to get the number of people needed in each group using the following formula:

=ROUNDUP(B2/C2,)

This works as intended.

Cell D2 put the number of people in to the cell using the following formula.

=TEXTBEFORE(A2,", ",D2)

this allows the 1st group to pull the values before , of the last person in group 1 and acts as intended.

the issue I'm running into is from cell F2:BB2. I need a way to display values between the , that the formula:

=TEXTAFTER(A2,", ",D2,1,0) 

shows.

the issue with the formula above is it dumbs all values not in group one into group 2... which i'm not 100% sure how to fix.

An example of this would be if I needed 10 groups with 200 people group one would display any value before the 20th comma ( from a mixed list of the list in a2, I haven't gotten that far...) and for group 2 it would need to display the string after comma 20 but before comma 40, group 3 would need to display the values starting after comma 40 but before 60 and so on... my thought was to use a if formula but it has not panned out for example...

=if(=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1 > d2, (display the string starting at comma x until the value in d2 is met),"")

The issue is I'm not 100% use the best way to make this part happen.

below is a copy of my attempts to make this idea work. thank you for any help you can give on the matter

https://1drv.ms/x/s!AuFetCdegkxpgQ2coheHEuNMuOjI?e=Tm3OMU

Answer 1

You can split the list with a single formula, and the results will spill to the relevant number of columns.

For example, with your text string in A2 and your number of splits in B2, the following formula:

=TEXTSPLIT(
    TEXTJOIN(
        "~",
        ,
        BYCOL(
            LET(
                sp, TEXTSPLIT(A2, , ","),
                n, ROWS(sp),
                cols, IFERROR(
                    WRAPCOLS(sp, CEILING(n / B2, 1)),
                    ""
                ),
                cols
            ),
            LAMBDA(arr, TEXTJOIN(",", , arr))
        )
    ),
    "~"
)

You can randomize the list by adding a SORTBY line as shown below:

=TEXTSPLIT(
    TEXTJOIN(
        "~",
        ,
        BYCOL(
            LET(
                sp, TEXTSPLIT(A2, , ","),
                n, ROWS(sp),
                rs, SORTBY(sp, RANDARRAY(n)),
                cols, IFERROR(
                    WRAPCOLS(rs, CEILING(n / B2, 1)),
                    ""
                ),
                cols
            ),
            LAMBDA(arr, TEXTJOIN(",", , arr))
        )
    ),
    "~"
)

However, the list results will change every time the worksheet is recalculated, or even if an entry is made.

If that is not what you want to happen, you will need to copy the formula results and paste it as text someplace. You can do that manually or using VBA.

In the above:

• sp: Split the entire text string by the comma
• n: number of entries
• rs: randomized list of the split text strings
• cols: create vertical arrays with the requisite number of columns (computed as CEILING(n / B2, 1))
• TEXTSPLIT(... TEXTJOIN(... BYCOL(... converts the vertical arrays into the requisite number of comma separated strings