Friday, 13 May 2011

Generate list of non repeating numbers in Excel

At work we had to create a list of new telephone numbers for employees.

We had a list of employees and had to assign a list of telephone extensions to the names. This had to be random and each number could only be used once.

The we did this was to copy the list of names into Excel. Then we used the RAND function to assign a random number between 0 and 1 to each name. After this we sorted the list by the newly created random number column. This gave us a random list of users. Finally we copied in the sorted list of telephone extensions into a column next to the employee name column. This gave a random list of telephone extensions to employee names without any any repetition.

Below is an example of what we did:

With a column for your list of names and a column for the random number you can randomise your list of names by simply ordering the list by the random number column. When you are happy with the random list of names then all you have to do is enter your sequential list of numbers into another column.