+ Reply to Thread
Results 1 to 14 of 14

if restriction is not met, select another word randomly from the list

  1. #1
    Registered User
    Join Date
    07-10-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    8

    if restriction is not met, select another word randomly from the list

    Hi!

    I hope someone will help me with the following question. I am trying to make a task in which words from two different word categories will appear randomly on the screen. In total we have 80 words; 20 from category 1 and 20 from category2; each word is repeated twice. I want these 80 words in my task; so no missings or words that are repeated three times.

    A restriction of this randomization is that we want that three words from the same category never appear subsequently. Another restriction is that we do not want the same words occur in succession.

    I have managed to organize the word lists randomly. However, I don’t know how to say IF C2 AND C3 AND C4 are all words from category1 THEN take another word randomly from the list and replace C4. And continues this loop until C4 is a category 2 word.

    An additional problem is that it is possible that at the end from the list I only have 3 words from the same category. Is it possible that excel repeat the whole function until the whole list is completed?

    All suggestions are welcome!

    Best,
    Bloem

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: if restriction is not met, select another word randomly from the list

    Could you explain using numbers, e.g., the numbers 1 to 20 are in list1, each appearing twice, the numbers 21 to 40 are in list2 each appearing twice, and each arrangement consists of ...?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    07-10-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: if restriction is not met, select another word randomly from the list

    Of course I can do that. In your example the number 1 till 20 belong to category 1, and 21 till 40 belong to category 2. I want these numbers appear randomly in a new column. If I use rand() it is possible that three (or more) numbers of the same category (eg 1) come below each other in the column. I want to avoid this. For example if C1 = 15 and C2 = 3, then C3 has to become a number between 21 and 40.
    And the other restriction is that if C1 = 15, than C2 may not be 15.
    Finally, if the last three numbers left are only numbers between 21 and 40, then there is no solution possible. I would like that the whole loop starts over and that he tries another randomization until I have a good list.
    I hope my problem is clear now. If not, please let me know.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: if restriction is not met, select another word randomly from the list

    So the 20 numbers do indeed appear twice in each list? Can an arrangement include the same number twice?

    What's the purpose of this?

  5. #5
    Registered User
    Join Date
    07-10-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: if restriction is not met, select another word randomly from the list

    I do not understand what you mean with “Can an arrangement include the same number twice?”
    I am trying to make a cognitive behavioral task for in the MRI scanner. We will measure reaction times when words of different word categories appear on the screen as distractors of a different reaction time task (not described here). We hypothesize that words from one category are more distracting than from the other category.

  6. #6
    Registered User
    Join Date
    07-10-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: if restriction is not met, select another word randomly from the list

    Sorry, it took a moment to understand what you meant. But yes, in the new column each number has to appear twice. The only restriction is the same numbers does not occur in succession.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: if restriction is not met, select another word randomly from the list

    Sorry to be slow, but could you please give some examples of acceptable and unacceptable outputs, using numbers instead of words?

  8. #8
    Registered User
    Join Date
    07-10-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: if restriction is not met, select another word randomly from the list

    I have in column A the numbers randomly organized. For example the first numbers are 1 7 3 36 16 10 10 (=A1 till A7). The first numbers 1 and 7 are fine. The problem is that I do not want number 3 in succession, because this number belongs also to category 1. Instead Excel has to select a number for column A above 21 (for example 36). He should remember that he has selected A4 (=36) already and that he have to put A3 (=3) at another place in the new column.
    So for example he has now 1 7 36 in the new column. In that case, it does not matter whether there appears a number beneath 21 or above 20 in the column.
    The last restriction is that the numbers 10 are in succession of each other. So he has to replace number 10 with another number from column A, like explained before.
    Please say it whether something is still unclear. I am not that familiar with excel, so probably I explain it weird.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: if restriction is not met, select another word randomly from the list

    What would be more helpful is if you create a mockup of a workbook with a candidate list of outputs. For rows that are not valid, explain why.

    On another worksheet, show the list of numbers in each category.

  10. #10
    Registered User
    Join Date
    07-10-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: if restriction is not met, select another word randomly from the list

    Thanks for all your help! I made a worksheet. However I do not know how to upload an attachment to this site… Please, can you help me with this question too??!

  11. #11
    Registered User
    Join Date
    07-10-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: if restriction is not met, select another word randomly from the list

    I think I attached the file...
    Attached Files Attached Files

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: if restriction is not met, select another word randomly from the list

    See attached. You'll need to enable macros; I could have created a formula that does the same thing, but it would have been ugly.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-10-2013
    Location
    Nijmegen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: if restriction is not met, select another word randomly from the list

    Thank you very much! I can work with this

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: if restriction is not met, select another word randomly from the list

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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