Create random list of strings in random size in excel

Question

I have the following data in my excel.

A
Text 1
Text 2
Text 3
Text 4
Text 5
Text 6

I want to fill column B with random joined data from column A. It should respect the criteria 1> B > 6. i.e. Column B should have a min 1 value from column A or can have a max of up to 6 unique values joined by ,. I can have column B dragged up to 100 rows. But still, they should respect the criteria.

I'm able to get a random value from column A using the formula INDEX($A$1:$A$6, RANDBETWEEN(1, ROWS($A$1:$A$6)), 1), and to join 2 random texts I'm using the formula

=TEXTJOIN(",",true, INDEX($A$1:$A$6, RANDBETWEEN(1, ROWS($A$1:$A$6)), 1), INDEX($A$1:$A$6, RANDBETWEEN(1, ROWS($A$1:$A$6)), 1))

Currently, I'm able to get 2 fixed strings using this formula. Instead of doing the above 6 times, I want to know If there is a way to get this joined string with a random number of unique strings(of the max size of column A length concatenated with a ,).

I'm able to get only 1 value using the random function. Please let me know how can I do this.

Answer 1

You could try:

Formula in B1:

=TEXTJOIN(",",,TAKE(SORTBY(A1:A6,RANDARRAY(COUNTA(A1:A6))),RANDBETWEEN(1,6)))

---

Note that TAKE() is a new function which is still in BETA. If you don't have access just yet, then try:

=TEXTJOIN(",",,INDEX(SORTBY(A1:A6,RANDARRAY(COUNTA(A1:A6))),SEQUENCE(RANDBETWEEN(1,6))))

---

In each option:

• SORTBY(A1:A6,RANDARRAY(COUNTA(A1:A6))) - Will create a randomized array of the values in column A;
• RANDBETWEEN(1,6) - The part which defines the lower- & upper-limit of strings to concatenate;
• TAKE/INDEX - A way to retrieve an X amount of rows from the above randomized array. In your case X itself is randomized (see 2nd bullit);
• TEXTJOIN() - Concatenate all selected values into a single string.