Discussion Locked

Random # generator in Excel

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a list of folks (in Excel) that are assigned specific tasks during inventory time.

I need to come up with a way to randomly assign a number to the 14 people (a number between 1 & 14), that is not repeated for any of the employees.

Now here's the kicker,... This needs to be done in 4 diffirent columns (for the same people) and no one can have the same number more than once.

So, if "Employee X" is in column A, then columns B, C, D & E should all have a different random number generated. This needs to continue for all 14 people. And, all numbers in column B have to be different.

All 14 employees need to be assigned a random number (1 thru 14) in column B. As well as columns C, D & E. Bu, no one can have been assigned a number more than once. (ie: Employee X can be assigned #'s 1,5,10,14. But not #'s 1,5,10,5 - because the 5 is listed twice.

Any suggestions??

4 replies

by dklippert

I?m sure there is a more elegant solution.

A B C D E
_____________________________________
Name One Two Three Four

In B2 I put the formula

=RANDBETWEEN(1,14)

In B3

=IF(B2<14,B2+1,1)

B3?s formula is filled down column B.
I then entered in C2, the formula

=IF(B2<14,B2+1,1)

That formula is filled down column C and filled across D and E

(You need the Analysis ToolPak activated to get RANDBETWEEN)

This discussion is closed to new replies.