Randomly Distribute Specific Values Without Repetition Using Excel

Question

I need to randomly distribute 1000 customer phone numbers to 25 Customer Care Agents without repetition using Excel.

I have tried using RANDBETWEEN function but the phone numbers are non-consecutive while numbers were repeated.

Can this be achieved on Excel?

Answer 1

If you have the phone numbers in A1:A1000 then put this formula in B1

=RAND()

and copy all the way down to row 1000 (you can do that easily by double clicking on the "fill-handle" in B1 - the black "+" you see if you put the cursor on the bottom right of the cell).

Now select both columns A and B and sort by column B - that will sort your phone numbers randomly, so just allocate the top 40 to the first Agent, the next 40 to the next agent etc.