Five random items from a a list into a single cell separated by a comma

Question

I have n number of unique values in n cells in Column A. (For ex: EDN12, EDN122, EDN991, ....)

I want to return any five unique values without repetition in a random order from Column A into an individual cell n times separated by a comma. For example; (EDN12, EDN112, EDN991, EDN881, EDN12)

How do I achieve this?

I have tried this formula provided here (Return a random order of a list into a single cell )

=TEXTJOIN(",",,INDEX($A$1:$A$5,UNIQUE(RANDARRAY(1000,1,1,5,TRUE))))

But it only generates five values for starting five cells in column A and rest are omitted.

Answer 1

This is an alternate formula to get the required results without using LET.
Although I prefer the solution using the LET function.

=INDEX(A3:A22,INDEX(UNIQUE(RANDARRAY(COUNTA(A3:A22),1,1,COUNTA(A3:A22))),SEQUENCE(5)))

Breaking it down:

Get an array of random numbers based on the number of data rows.

=RANDARRAY(COUNTA(A3:A22),1,1,COUNTA(A3:A22),TRUE)

Extract the unique values from the array of random numbers.

=UNIQUE(C3#)

Extract the first five unique values

=INDEX(D3#,SEQUENCE(5))

Use the extracted values to extract matching rows from the source data.

=INDEX(A3:A22,E3#)

Finally join the values into a single cell.

=TEXTJOIN(", ",TRUE,F3#)

If your list of data is very short, then it can return non-unique values.
Although your example appears to have at least 1000 data rows, so it will not be a problem.

Answer 2

Assuming values in column A are unique on their own, try:

=LET(x,TOCOL(A:A,3),TEXTJOIN(", ",,TAKE(SORTBY(x,RANDARRAY(COUNTA(x))),5)))

Otherwise just nest 'x' in UNIQUE():

=LET(x,UNIQUE(TOCOL(A:A,3)),TEXTJOIN(", ",,TAKE(SORTBY(x,RANDARRAY(COUNTA(x))),5)))

---