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

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.

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.

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.

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.

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??!

I think I attached the file...

Thank you very much! I can work with this