Search code examples
excelshuffle

Shuffle column in Excel but ea. value appears equal amount of times

I need to shuffle a column and make sure each value appears equal amounts of time.

I.e.

Bob          Joan
Clara        Bob
Joan         Ronald
Ronald       Clara

I tried =SORTBY(B2:B65,RANDARRAY(ROWS(B2:B65))) but this does not do what I need.

Edit:

These names are currently alphabetized but they need to be randomized and they can't appear immediately after each other, too. So i.e.

Clara
Clara 
Bob 
Ronald 
Joan 
Ronald 
Bob 
Joan

… would not be ok either.

Solution

  • This will randomize the list. It will use the whole list before creating dups. so no matter how many in the output list there will be as even a distribution as possible depending on the ratio of output names vs the input list.

    =LET(rng,B2:B5,
    rw,ROWS(rng),
    num,E2,
    clm,ROUNDUP(num/rw,0),
    TAKE(INDEX(rng,UNIQUE(TOCOL(RANDARRAY(10*rw,clm,1,rw,TRUE)+SEQUENCE(,clm)/10,,TRUE))),num))

    enter image description here

    One note: There is no guarantee that the last name of the preceding random group will not be the first of the next random group and as such they may be one right after the other.

    enter image description here

    But the more names in the list the less likely this will happen.