+ Reply to Thread
Results 1 to 25 of 25

Complex Random Cell Extraction checking multiple criteria

  1. #1
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Complex Random Cell Extraction checking multiple criteria

    Hi everyone, I've been stuck on this for hours and can't seem to get it to work, though I know what I'm trying to do is a bit complicated. Wish I could post pictures .

    First of all I'm working from 2 sheets in the same workbook:
    Randomizer and Lyrics

    Lyrics sheet has 3 columns:

    A2:A14 = Theme Tags
    B2:B14 = Category Tags
    C2:C14 = Lyrics

    Each Lyric Line can have one or more theme /category tags associated with it

    For Example:

    A7 could look like "Theme1, Theme2" meaning that the lyric in that row fits both themes.

    On the Randomizer Sheet, I have a theme drop down where the user selects the theme. Only one theme can be selected at any given time. There are 4 lines generated from the lyric based on the theme selected.

    The rules for each line:
    1) Selecting the theme from the drop down finds a random line that fits the theme and puts it in line 1
    2)It then generates a random line that fits the theme(same theme tag) and fits the category (same category tag) of line 1 and is not line 1 and puts it in line 2
    3)It then generates a random line that fits the theme(same theme tag) and is not line 1 or line 2 puts it in line 3 (doesn't care about category)
    4)It then generates a random line that fits the theme(same theme tag) and fits the category(same category tag) of line 3 and is not line 1 or line 2 or line 3 and puts it in line 4

    Basically I'm trying to create a poetry generator where the user can set the theme and it will generate lines that can rhyme the first 2 and second 2 lines (AABB). The Category will later become phonetic ending syllables. But I'm trying to just get the excel sheet working before filling it with content.

    What I have for Line 1 cell in the randomizer is:

    =IF(ROWS(G$11:G11)<=$I$1,INDEX(Lyrics!C2:C14,SMALL(IF(ISNUMBER(SEARCH("*"&Criteria&"*",Lyrics!A2:A14)),ROW(Lyrics!A2:A14)-ROW(Lyrics!A2)+1),ROWS(G$11:G11))),"")

    Important info:

    G11 = Line 1
    G12 = Line 2
    G13 = Line 3
    G14 = Line 4
    Criteria is the theme dropdown cell in the randomizer
    I1 =COUNTIF(Lyrics!A2:A14,"*"&Criteria&"*")

    Currently it pretty easily detects the theme, even when a line could be tagged for multiple. However the randomization is proving to be very difficult and not really sure where/how to do the category searching.

    I realize this may be hard to debug without seeing the file. It is not for work or anything so I can freely send it to anyone if you need to take a closer look.

    Thanks

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    You can add a file to a post by clicking the "Go Advanced" button below your post, and then choosing the paper clip icon to choose, upload, and insert a file.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    Poetry_Randomizer.xlsx

    Thank you

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    To completely randomize, you need RAND() functions somewhere - so I have added four columns of conditional formulas to your lyrics sheet. Give it a try. Re-calcing (pressing Ctrl-Alt-F9) should create a new poem - when you add more lines, then the problem with line 9 should disappear (no matching theme/category lines).

    Poetry_Randomizer with formulas.xlsx

  5. #5
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    Thanks, Bernie! This helped a ton. I have another question.

    How can I make it so double clicking doesn't re-randomize. Ideally I'd like users of the sheet to click around the sheet without it randomizing. Instead I'd like a button below the the theme to Start the randomization. Repressing the button will randomize the options depending on the theme selected. Changing the theme without pressing the button would then not trigger the randomize either. How would I do that?

    Thanks in advance!

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    You would need to use a macro - attached is a version that uses this macro code. When the workbook recalculates, the poem will not change. Note that you will need to allow macros for this to work.



    Please Login or Register  to view this content.
    Poetry Randomizer with formulas and macro.xlsm

  7. #7
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    Cool, everything seems to work except if I have multiple category tags in the same cell.

    I want it to behave like the theme tags, where a lyrics line can have multiple category tags assigned to it.

    Example: A:7 could contain Category1, Category2, Category 5.

    Is that something that is possible to do?

  8. #8
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    In the example I made... I meant B:7 could contain Category1, Category2, Category5... sorry

  9. #9
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    The easiest way to do this is to simply triple the lyric line - by that I mean have three lines with

    Theme1 Category1 LineX
    Theme1 Category2 LineX
    Theme1 Category5 LineX

  10. #10
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    That is indeed a way it would work.. Though I'd like a cleaner solution if possible. I don't like the idea of duplicating lyric line entries but I'm not sure what other options there are. BTW, thank you so much for the help on this. I really appreciate it

  11. #11
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    Here's the problem - matching

    Category 1, Category 3, Category 5

    with

    Category 3, Category 4, Category 6

    or

    Category 1, Category 6

    with

    Category 3, Category 4, Category 5, Category 6, Category 8


    Starts to involve many many many more formulas - or VBA completely - so unless the user decides which category to use for the first of the pair of lines (to match the other line's category, or, alternatively, not match the third line's category) it is much better to enter the lines once for each category.

  12. #12
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    Yeah, you are right, I think it's a good call to just duplicate the line. I only have one more question, and again not sure this is possible...

    Searching for certain text types and dynamically change macros to randomly replace that text from a list.

    The Four lines that are randomly picked need to be searched through for a string format that I've specified. The first string found under that format must be copied to the first button, the second string found under that format must be copied to the second button… etc. I have a max of eight buttons, because each line of the 4 lyric lines can have 1 or 2 strings under this format. I use “%” before and after the string as the format. Here is an example of one of the 4 lines:

    The %sound% of my own %instrument% is strong and loud.

    In this case I’d want “%sound%” to be copied to button 1 and “%instrument%” to be copied to button 2.

    That is the first step. The next step is that I’d like these buttons to then do a random text replace with another word from another list. There is another sheet called “Blanks” that has a list of words that fit the lyric. Basically The words with the special format are interchangeable with the words from the lists and the user can cycle through the different words randomly by hitting the button. The macros must not randomize the lines, but instead just randomize the string formatted word it's associated with.

    I’ve tried a few different ways (SEARCH, REPLACE, FIND, and SUBSTITUTE) but it hasn’t worked with my current setup. Having problems getting the macro to know what list to pick from.

    Attached is the updated file. If you need any more clarification, please let me know. I’ve been trying to do it with a number of functions and can’t get it to do what I want. Thanks ahead in advance.Poetry_Randomizer.xlsm

  13. #13
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    But, for example, do you want to allow two lines like this?

    Not just another %part_of_the_body% in the crowd
    So if you use your %part_of_the_body% you will know why I stand proud

    Should each be assigned to a separate button? Should part_of_the_body be the same for those two?

  14. #14
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    Each would be assigned to a separate button. In the case you are describing, it wouldn't be the same for those two to start. I don't mind having the word be the same if the player keeps hitting the macro to cycle randomly through the list of words under that formatted string.

  15. #15
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    Give this a try:


    Poetry_Randomizer 2.xlsm

  16. #16
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    It says, "object doesn't support this property or method when I hit the buttons, is there something else i need to enable?

  17. #17
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    I use Excel 2010, and you use 2007, so they should be OK. Which line (press debug when you get the message) gives the error?

  18. #18
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    Looks like

    Me.Shapes("Rounded Rectangle " & i).Title = ""

    on line 7 when I hit the "Rap" button and

    v = Split(ActiveSheet.Shapes(strButtonName).Title, " ")

    in Module1 when I click on the formatted string macro buttons

  19. #19
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    Try this version:

    Poetry_Randomizer 3.xlsm

  20. #20
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    it randomizes the word, but now changing the dropdown and hitting the "rap" macro either crashes it or doesn't update the text in the Line Fields (the format string macros seem to be working fine though)

  21. #21
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    Somehow, events got turned off - try this version:

    Poetry_Randomizer 4.xlsm

  22. #22
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    I appreciate you really looking into this. I still get an argument not optional error when I choose a different theme. the "rap" still doesn't update the lines

  23. #23
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria


  24. #24
    Registered User
    Join Date
    09-11-2015
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Complex Random Cell Extraction checking multiple criteria

    This works perfectly! Thanks so much for the help! I'm not very familiar with VBA coding but I guess I'll need to start learning. Is there a way to prevent the macro buttons themselves from changing from the formatted string every time you push it? And in VBA how would I change the format of text that that is pulled. Ideally I'd like the randomized formatted strings to be bold and a different color.

  25. #25
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,098

    Re: Complex Random Cell Extraction checking multiple criteria

    Give this a try:

    Poetry_Randomizer 6.xlsm

+ 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. multiple criteria search and extraction from string!
    By oasafox in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-28-2014, 07:27 AM
  2. [SOLVED] Complex data extraction from a table
    By Corke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-14-2013, 11:49 AM
  3. Checking multiple criteria and select data over multiple columns
    By jy677 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2013, 09:38 PM
  4. [SOLVED] complex matrix data counting and extraction problem
    By Red fuji in forum Excel General
    Replies: 50
    Last Post: 10-01-2012, 12:39 AM
  5. Complex Filtering with Multiple Criteria
    By corpusworker in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-02-2008, 06:58 AM
  6. Complex calculation including extraction of month from date field
    By arbourp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-01-2007, 04:08 AM

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