Random numbering

[hopelessvolunteer]

Hello all, new to the board.

I have a list of names in Column A going from row 2 to 15. I want to randomly assign them a number ranging from 1-14, but that random number can not be assigned twice. I only need each number once.

I am putting the formula in column B.

Can someone please help me.

Thanks!!

 

[barry houdini]

With a helper column, e.g. in C2 copied down to C15

=RAND()

in B2 down

=RANK(C2,C$2:C$15)

hide column C if you wish

if worksheet is re-calculated numbers will change (hit F9 to recalculate)

 

[hopelessvolunteer]

random numbers

How do I keep the numbers from re-calculating each time I switch pages in the worksheet?

Thanks

 

[hopelessvolunteer]

Random Numberin

Also, how to I ensure that if the same person is in Column A and in Column D, they do not get the same random number. Thank you very much, it is greatly appreciated.

 

[hopelessvolunteer]

Random Numbering

How do I sort this, once I put in the formula's, when I choose a sort is does not sort it. Thanks

 

[Diffy]

I am not sure about your other questions, but excel won't sort formulas. So you would have to copy and paste the Values in a different sheet to sort.

 

[tusharm]

Re: Random Numbering

Hello all, new to the board.

I have a list of names in Column A going from row 2 to 15. I want to randomly assign them a number ranging from 1-14, but that random number can not be assigned twice. I only need each number once.

I am putting the formula in column B.

Can someone please help me.

Thanks!!

Also, how to I ensure that if the same person is in Column A and in Column D, they do not get the same random number. Thank you very much, it is greatly appreciated.

How do I sort this, once I put in the formula's, when I choose a sort is does not sort it. Thanks

How do I keep the numbers from re-calculating each time I switch pages in the worksheet?

Thanks

Why don't you figure out what it is you really want/need and then make a single comprehensive post? That way you get a solution to your real problem.

 

[hopelessvolunteer]

Random

What I am trying to do is this:

I have 2 columns (B and C) of names, some of the names will be the same in each column.

I need to randomly pick one name from each column, until all the names in each column are picked only once. But the random picked name can't duplicate. For example: if George is in column B and C, when the random pick is done George can not pick himself.

Hope this makes sense and is better describing what I need to get done.

Thanks.

 

[tusharm]

Re: Random

What I am trying to do is this:

I have 2 columns (B and C) of names, some of the names will be the same in each column.

I need to randomly pick one name from each column, until all the names in each column are picked only once. But the random picked name can't duplicate. For example: if George is in column B and C, when the random pick is done George can not pick himself.

Hope this makes sense and is better describing what I need to get done.

Thanks.

OK, so there's nothing in A or D. No formulas in B. Correct?

Also, if George is in B and he cannot pick George in C, how can Jane in B pick George in C? After all, George in B might pick Karl in C!

If you want truly unique pairings, put all the names in a single column, remove duplicates with Data | Filter > Advanced Filter... and then use the previously indicated technique for creating a random list. Now, pair 1 & 2, 3 & 4, etc.

For more ways to generate a list in random order see
Random Selection
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html

 

[hopelessvolunteer]

Random Numbering

What I am doing is a spreadsheet for the sport of Team Roping, where you have a header and a heeler. People come and enter and then randomly draw their partners. But sometimes one person will enter Heading and Heeling. I used the example above as George entered heading and heeling.

With the help of your last post here is what I have done.

Column A has all of the Headers names in it,
Column B has the following formula to randomly draw a Heeler, =INDEX($C$2:$C$14,RANK(D2,$D$2:$D$14))
Column C has the Heelers names in it
Column D has the following formula in it, =RAND()

I need to know if there is a way that I can put in the formula, so the same name does not appear in Column A row 2 Column B row 2?

And how do I copy and paste just the values, after I have done the random pick, not the formula?

Thank you so much for your help.