I have a big list with about ~300 hashtags where I want to pick a certain amount (like 10 hashtags) randomly. Is it possible without VBA?
CodePudding user response：
Here's one way of doing it, though it relies on your Excel being recent enough to support these functions:
- LET
- UNIQUE
- RANDARRAY
- SEQUENCE
With this formula:
=TEXTJOIN(" ",,LET(rng,$B$3:$B$13,n,ROWS(rng),idx,UNIQUE(RANDARRAY(n*n,1,1,n,TRUE)),s,SEQUENCE($E$1),INDEX(rng,INDEX(idx,s))))
It's a bit lengthy, but works around the observation that RANDARRAY() can return duplicate values by getting many more random numbers than are needed, and then taking the first x values.
NB. If the number of tags you want is small compared to the total available, then you probably don't need the (n*n) and can just use (n).
Hat-tips to @SpencerBarnes and @MayukhBhattacharya
CodePudding user response：
on Office 365:
=CONCAT(INDEX(A1:A300, RANDARRAY(10,0, 1, 300, TRUE)))
Where A1:A300 is your list of hashtags, and 1, 300, is the start and end of the list.
Removing the CONCAT() function from the outside of the formula will output a spilled range rather than a concatenated string.