Generate random group of words in a cell from a large group word list in column

Hi Everyone,

Need your help with the following:

I have a list of 100 words/hashtags (single hashtag in each cell with no space) in range A1:A100

What I need is a group of 30 random words/hashtags into cell B1, 30 random words/hashtags in B2, 30 random words/hashtags in B3 and so on...

I have a screenshot which shows what it will look like.
https://i.imgur.com/ZQV8VBF.jpg

=============

What do I need this for:
I have a list of 1000 instagram posts in excel, instagram allows to use 30 hashtags in a single post, I have a like 100 tags to use and need a group of 30 random tags for each post.

Thanks for your response!

No the hashtag cannot be duplicated in the same cell. I need to do this for over 1000 cells. You can download the sample file with before after example here: https://coldplaying.files.wordpress....r-example.xlsx

No the combination doesn't need to be unique, any combination of 30 will work.

I have added the attachment

Thanks, really appreciate your help and consideration.

If I may using helper columns?

Formula for helper column D, put this on D4, ENTERED as ARRAY Formula:

=INDEX($A$2:$A$122,LARGE(MATCH(ROW($A$2:$A$122),ROW($A$2:$A$122))*NOT(COUNTIF($D$3:D3,$A$2:$A$122)),RANDBETWEEN(1,ROWS($A$2:$A$122)-ROW(A1)+1)))

Formula for helper column E, put this on E4, ENTERED as ARRAY Formula:

=INDEX($A$2:$A$122,LARGE(MATCH(ROW($A$2:$A$122),ROW($A$2:$A$122))*NOT(COUNTIF($E$3:E3,$A$2:$A$122)),RANDBETWEEN(1,ROWS($A$2:$A$122)-ROW(A1)+1)))

Formula for helper column F, put this on F4, ENTERED as ARRAY Formula:

=INDEX($A$2:$A$122,LARGE(MATCH(ROW($A$2:$A$122),ROW($A$2:$A$122))*NOT(COUNTIF($F$3:F3,$A$2:$A$122)),RANDBETWEEN(1,ROWS($A$2:$A$122)-ROW(B1)+1)))

Formula for helper column G, put this on G4, ENTERED as ARRAY Formula:

=INDEX($A$2:$A$122,LARGE(MATCH(ROW($A$2:$A$122),ROW($A$2:$A$122))*NOT(COUNTIF($G$3:G3,$A$2:$A$122)),RANDBETWEEN(1,ROWS($A$2:$A$122)-ROW(B1)+1)))

AND THIS FOR RESULTS:
Put this on B2:

=CONCATENATE($D$4,$D$5,$D$6,$D$7,$D$8,$D$9,$D$10,$D$11,$D$12,$D$13,$D$14,$D$15,$D$16,$D$17,$D$18,$D$19,$D$20,$D$21,$D$22,$D$23,$D$24,$D$25,$D$26,$D$27,$D$28,$D$29,$D$30,$D$31,$D$32,$D$33)

Put this on B3:

=CONCATENATE($E$4,$E$5,$E$6,$E$7,$E$8,$E$9,$E$10,$E$11,$E$12,$E$13,$E$14,$E$15,$E$16,$E$17,$E$18,$E$19,$E$20,$E$21,$E$22,$E$23,$E$24,$E$25,$E$26,$E$27,$E$28,$E$29,$E$30,$E$31,$E$32,$E$33)

Put this on B4:

=CONCATENATE($F$4,$F$5,$F$6,$F$7,$F$8,$F$9,$F$10,$F$11,$F$12,$F$13,$F$14,$F$15,$F$16,$F$17,$F$18,$F$19,$F$20,$F$21,$F$22,$F$23,$F$24,$F$25,$F$26,$F$27,$F$28,$F$29,$F$30,$F$31,$F$32,$F$33)

For last, put this on B5:

=CONCATENATE($G$4,$G$5,$G$6,$G$7,$G$8,$G$9,$G$10,$G$11,$G$12,$G$13,$G$14,$G$15,$G$16,$G$17,$G$18,$G$19,$G$20,$G$21,$G$22,$G$23,$G$24,$G$25,$G$26,$G$27,$G$28,$G$29,$G$30,$G$31,$G$32,$G$33)

Originally Posted by azumi

If I may using helper columns?

Formula for helper column D, put this on D4, ENTERED as ARRAY Formula:

=INDEX($A$2:$A$122,LARGE(MATCH(ROW($A$2:$A$122),ROW($A$2:$A$122))*NOT(COUNTIF($D$3:D3,$A$2:$A$122)),RANDBETWEEN(1,ROWS($A$2:$A$122)-ROW(A1)+1)))

Thanks Azumi and Zer0Cool for your support.

I have made some modification to the workbook from Azumi and I think I found a workaround. Please check the attached workbook, the green column is where we get the results.

What I did:
If we can do 'Helper Columns' by horizontally picking the 30 unique hashtags (instead of vertical) we can easily generate 100's of unique group of 30 hashtags or words using concatinate by easily pulling down the range.

@Azume could you please provide us with the function that will not duplicate the hashtags horizontally. I wasn't able to do it (I've never used NOT, LARGE & ROW function before) Thanks a lot!