Create random list of strings in random size in excel

user_3872094 0
Viewed 108

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.

excel-formula excel
3 Answers

You could try:

enter image description here

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.
user_97581940

This formula exploits the functions RANDARRAY and RANDBETWEEN to get a random number of text items to join.

First, I created a dynamic named range called AllTextItems. This automatically expands to capture any number of rows in your dataset:

enter image description here

Then, use the formula:

=TEXTJOIN(",",TRUE,INDEX(AllTextItems,RANDARRAY(RANDBETWEEN(1,ROWS(AllTextItems)),1,1,ROWS(AllTextItems),TRUE)))

in the cells you'd like your joined list.

enter image description here

user_47177550 
=TEXTJOIN(",", TRUE, INDEX(A:A, RANDARRAY(1, RANDBETWEEN(1, COUNTA(A:A)), 1, COUNTA(A:A), TRUE)))

EDIT didn't quite beat JvdV to it, but basically a similar basis to his but not using TAKE and not sorting the output

user_122319840
Related