View original (French)

Alea between bounds without duplicates.

nuclecanon Posted messages 267 Status Member -  
Le Pingou Posted messages 12274 Registration date   Status Contributor Last intervention   -

Hello,

I created a deck of 40 playing cards. On each of these cards, I would like to randomly put the order of the 8 players to resolve events in the game.

Example 1: Card number 1 will have the players play in this order: BLUE, YELLOW, RED, GREEN, PURPLE, ORANGE, PINK, CYAN

Example 2: Card number 24 will have the players play in this order: CYAN, PURPLE, BLUE, ORANGE, YELLOW, GREEN, PINK, RED

I used the formula =RANDBETWEEN(1;8) across the entire row but I am getting duplicates... I replaced the players' colors with numbers. I do not want to use VBA.

Note that there are more than 40,320 permutation possibilities... that makes a lot of cards lol. I will take the 40 most balanced possibilities.

Thank you =)

4 answers

  1.
    Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     

    Hello.

    Since it's a random draw, chance could even pull the same player 8 times!

    I suggest running your formulas and manually correcting the duplicates...

    Retirement is great! Especially in the Caribbean...
    Raymond (INSA, AFPA)

    0
  2. nuclecanon Posted messages 267 Status Member 8
     

    Hello,

    [...]chance could even draw the same player 8 times! [...] That's exactly where it gets complicated.

    I stressed about it so much that, as a result, I corrected it manually before you even told me to do so.

    But out of simple curiosity, is there any way to do it without duplicates?

    0
  3. yclik Posted messages 3874 Registration date   Status Member Last intervention   1 609
     

    Hello

    A tip

    Formula in A2=RAND.BETWEEN(1;40)+ROW()*0.0001
    Formula in B2=RANK(A2;A$2:A$41)
    Formula in C2=VLOOKUP(B2;$M$2:$N$41;2;0)

    0
  4. Le Pingou Posted messages 12274 Registration date   Status Contributor Last intervention   1 476
     

    Hello yclik,

    I searched in vain by function (because of no VBA), so congratulations for this lead..!

    Thank you

    Regards.
    The Penguin

    0