Shuffle comma-separated list of values in one cell (Google Sheets)

user_10331671 0
Viewed 267

I'm wondering if anyone could give me a helping hand in shuffling a comma-separated list of values in one cell in Google sheets. My data looks like this: 

peter, andrew, mike, michael, ..., thomas

I'd like to randomly shuffle this string for each row and I have about a thousand rows that all have one cell that looks like this.

Any formula or link to a page where I can get closer to a solution would be greatly appreciated.

google-sheets-formula google-sheets
1 Answers

Draggable Single Formula

Here's a formula if you're ok with dragging a formula down:

=ArrayFormula(
    JOIN(
        ", ",
        Array_Constrain(
            SORT(
                TRANSPOSE(
                {
                    SPLIT(A1, ", ");
                    RANDARRAY(1,COUNTA(SPLIT(A1, ", ")))
                }),
                2,
            ),
            COUNTA(SPLIT(A1,", "))
            ,1
        )
    )
)

Two ArrayFormulas

It may be possible to combine these two into one, so I may revisit this later.

  1. Make sure you have three columns clear. I chose C as my start column.
=ArrayFormula(
    SORT(
    {
        ARRAY_CONSTRAIN(
            SPLIT(
                TRANSPOSE(
                    SPLIT(
                        TEXTJOIN(
                            ",",
                            1,
                            (SEQUENCE(COUNTA(A:A))-1)&"|"&SPLIT(FILTER(A:A&", ",LEN(A:A)),", ")
                        ),
                        ","
                    )
                ),
                "|"
            ),
            COUNTA(
                SPLIT(TEXTJOIN(", ",1,A:A),", ")
            ),
            2
        ),
        RANDARRAY(
            COUNTA(
                SPLIT(
                    TEXTJOIN(", ",1,A:A),
                    ", "
                )
            ),
            1
        )
    },1,1,3,1)
)
  1. Final result.
=ArrayFormula(
    TRANSPOSE(
        SPLIT(
            REGEXREPLACE(
                TEXTJOIN(
                    ", ",
                    ,
                    UNIQUE(
                        TRANSPOSE(
                            IF(
                                TRANSPOSE(FILTER(C:C,LEN(C:C)))=FILTER(C:C,LEN(C:C)),
                                FILTER(D:D,LEN(D:D)),
                                ";"
                            )
                        )
                    )
                ),
                ", (;, )+",
                ";"
            ),
            ";"
        )
    )
)

The tricky part was getting the "group concatenation" to work.

user_42943990
Related