+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    09-23-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    56

    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.

  2. #2
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

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

    Can a hashtag be duplicated in the same cell? Or does it need to be 30 unique and separate hashtags selected?

    IE: #me#me#me?

    How many cells do you need to fill this into? Is it just like 5 or do you need to do this for dozens or hundreds of cells?

    A sample file would be helpful as well with before and after examples.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  3. #3
    Registered User
    Join Date
    09-23-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    56

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

    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

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

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

    I am sorry I am unable to download files not attached here. If you do "Go Advanced", Manage Attachments, Choose file/upload you can attach it to a post here.

    Do the combinations need to be unique?

    IE: A, B, C and C, B, A

    The above are really the same thing, so can we only do one of these combinations or any combination of 30 unique hash tags?

  5. #5
    Registered User
    Join Date
    09-23-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    56

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

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

    I have added the attachment
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

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

    Ok, looking at it this would probably be best done via VBA/macro.

    A formula can certainly pick things at random, the problem becomes when its psuedo random (keeping track of past picks and excluding them isnt truely random) and a formula can only handle that so far. At 30 picks, keeping track of the previous 29 would likely not be feasible in a formula.

    Ill see if I can put something together soon

  7. #7
    Registered User
    Join Date
    09-23-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    56

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

    Thanks, really appreciate your help and consideration.

  8. #8
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,249

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

    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)
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

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

    Your profile states you have Office 2007. If thats not correct (and for others whom might find this) if you have Office 2016 with Office 365 subscription the TEXTJOIN function is the key to making this manageable to do via formulas.

    So as some background, and as @azumi's post clearly depicts, the problem is there isnt any clean way to concatenate a range or better yet, the results of an array formula. This is why they have to use the CONCATENATE() function with 30 references in it AND generate the list of random selections separately for each result.

    With the availability of TEXTJOIN, you can instead concatenate the results of an array formula. IE: an array returns {"A";"B";"C"} until TEXTJOIN we couldn't do anything to combine those results into a single string directly. TEXTJOIN can take that array and make it "A, B, C" instead.

    I dont have Office 365 handy so heres a a mockup of how it would work with formulas.

    The hashtags are in A2:A122, I used column B as a helper column (though this could possible be eliminated).

    Starting B2 filled down

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This generates random unique numbers from 1 to the count of the number of hash tags next to the hash tags.

    You can then get an array of 30 random entries using:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This would give something like {#hash1,"","",hash4,"",hash6, etc}.

    This is where we get stuck on Excel versions. Without Office 365 we cant do anything useful with this array to get a concatenated string from the array results. Thats where TEXTJOIN comes in.

    Otherwise the best bet would be either a UDF to immitate text join or a macro to fully generate the randomness and concatenation.

    Ill post a sample file when I can.

  10. #10
    Registered User
    Join Date
    09-23-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    56

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

    Quote Originally Posted by azumi View Post
    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!
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

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

    @faizzsheikh the formula will work horizontally when filled correctly.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In your file, the red bit should stay E3 and extend to F3, G3, H3, etc as you fill right. Instead your $E$3 was changed to match the row it was in preventing it from eliminating duplicates.
    Last edited by Zer0Cool; 07-26-2018 at 10:19 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Generate random combinations only one number from each group of 4 letters
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 08-18-2018, 12:01 PM
  2. [SOLVED] Macro to generate groups randomly of 20 numbers, being 10 of group A and 10 of group B,
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 11-14-2017, 08:02 AM
  3. Replies: 14
    Last Post: 01-12-2017, 01:41 PM
  4. [SOLVED] (VBA) How Assign Column Cell To Corresponding Group, and Paste Group Cells into Groups WS?
    By eryksd in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-08-2016, 06:18 PM
  5. Replies: 2
    Last Post: 07-07-2015, 02:32 PM
  6. Replies: 1
    Last Post: 02-10-2014, 10:26 PM
  7. Random sentence maker from group of words
    By xXNetRavenXx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-28-2010, 09:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1