Hi! I am trying to allocate a number of records that each need to be reviewed by 3 people, and randomly allocate them as the primary, secondary, and tertiary reviewer.
So I need to randomly generate 1, 2 and 3 in a random order next to each record, but the numbers cannot double up (ie cannot be 1,1,3 - every record must have 1 2 and 3).
I have so far easily randomly allocated 1 2 or 3 in the first cell (RANDBETWEEN) and then in the second cell used =SMALL(IF(A1={1,2,3},"",{1,2,3}),RANDBETWEEN(1,2)) but I have having trouble figuring out to say " if 1 and 2 is present, then insert 3. If 1 and 3 are present, then insert 2. If 2 and 3 are present, then insert 1".
Sorry that is probably a terrible explanation but hopefully someone understands what I'm trying to do!
So I need to randomly generate 1, 2 and 3 in a random order next to each record, but the numbers cannot double up (ie cannot be 1,1,3 - every record must have 1 2 and 3).
I have so far easily randomly allocated 1 2 or 3 in the first cell (RANDBETWEEN) and then in the second cell used =SMALL(IF(A1={1,2,3},"",{1,2,3}),RANDBETWEEN(1,2)) but I have having trouble figuring out to say " if 1 and 2 is present, then insert 3. If 1 and 3 are present, then insert 2. If 2 and 3 are present, then insert 1".
Sorry that is probably a terrible explanation but hopefully someone understands what I'm trying to do!
