Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I create and extract a random order from list in Excel?
Hi All,
I have a list of names and I want to randomly sort them and extract one of the sorted names in order, then randomise again, select, randomise, select etc... i.e. Say I have 4 names: Fred Betty Wilma Barney I want to randomly order the names, say it results in Fred 1st Wilma 2nd Barney 3rd Betty 4th I then want to have say the first name (Fred) copied to another cell, Say the list of names was in Cells A1:A4 so Fred would be copied to C1. Then the remainder of the list is randomised again resulting in say Betty 1st Wilma 2nd Barney 3rd Then Betty's name would be put in/copied to cell C2 Then the list is randomised again, and so on and so forth. Similar in a way to the way numbers for a lottery may be drawn, but I want to use Names, not numbers. Can Anyone Help? Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I create and extract a random order from list in Excel?
put your names in column A. In column B enter:
=RAND() and copy down In another column, say column C, enter: =INDIRECT("A" & ROW()) and copy down sort A & B by B this will randomize both A & B -- Gary''s Student - gsnu200740 "Michael" wrote: Hi All, I have a list of names and I want to randomly sort them and extract one of the sorted names in order, then randomise again, select, randomise, select etc... i.e. Say I have 4 names: Fred Betty Wilma Barney I want to randomly order the names, say it results in Fred 1st Wilma 2nd Barney 3rd Betty 4th I then want to have say the first name (Fred) copied to another cell, Say the list of names was in Cells A1:A4 so Fred would be copied to C1. Then the remainder of the list is randomised again resulting in say Betty 1st Wilma 2nd Barney 3rd Then Betty's name would be put in/copied to cell C2 Then the list is randomised again, and so on and so forth. Similar in a way to the way numbers for a lottery may be drawn, but I want to use Names, not numbers. Can Anyone Help? Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I create and extract a random order from list in Excel?
Thanks, that was sort of helpful, but unfortunately this does not allow a
re-randomisation using only the remaining three names in the example list, and then the remaining two names etc after the first name is drawn. Also it involves manually sorting the list. "Michael" wrote: Hi All, I have a list of names and I want to randomly sort them and extract one of the sorted names in order, then randomise again, select, randomise, select etc... i.e. Say I have 4 names: Fred Betty Wilma Barney I want to randomly order the names, say it results in Fred 1st Wilma 2nd Barney 3rd Betty 4th I then want to have say the first name (Fred) copied to another cell, Say the list of names was in Cells A1:A4 so Fred would be copied to C1. Then the remainder of the list is randomised again resulting in say Betty 1st Wilma 2nd Barney 3rd Then Betty's name would be put in/copied to cell C2 Then the list is randomised again, and so on and so forth. Similar in a way to the way numbers for a lottery may be drawn, but I want to use Names, not numbers. Can Anyone Help? Thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I create and extract a random order from list in Excel?
Why do it one at a time?
Why not just have your entire list re-display in random order? Say you place your original list in an out-of-the-way location, say Z1 to Z4. In Y1, enter the Rand function: =RAND() And copy down to Y4. Now, enter this formula in C1, and copy down to C4. =INDEX(Z$1:Z$4,RANK(Y1,Y$1:Y$4)) This gives you a random display of your names. Each time you hit <F9, you'll get a new random order. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Michael" wrote in message ... Thanks, that was sort of helpful, but unfortunately this does not allow a re-randomisation using only the remaining three names in the example list, and then the remaining two names etc after the first name is drawn. Also it involves manually sorting the list. "Michael" wrote: Hi All, I have a list of names and I want to randomly sort them and extract one of the sorted names in order, then randomise again, select, randomise, select etc... i.e. Say I have 4 names: Fred Betty Wilma Barney I want to randomly order the names, say it results in Fred 1st Wilma 2nd Barney 3rd Betty 4th I then want to have say the first name (Fred) copied to another cell, Say the list of names was in Cells A1:A4 so Fred would be copied to C1. Then the remainder of the list is randomised again resulting in say Betty 1st Wilma 2nd Barney 3rd Then Betty's name would be put in/copied to cell C2 Then the list is randomised again, and so on and so forth. Similar in a way to the way numbers for a lottery may be drawn, but I want to use Names, not numbers. Can Anyone Help? Thank you |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I create and extract a random order from list in Excel?
Yes, I would agree if that is what I want, but I do not want the entire list re-displayed in random order every time. The first time I want (n) names randomised The second time I want (n-1) names where the list does not include the first name from the first randomised list. If I randomise all again, I risk getting duplicate names. The third time I want (n-2) names randomised and so on. Ultimately I will take the selcted names drawn and then match/cross reference them to two/three other lists, I have a good idea how to do the cross referencing, but am stuck on the randomising of a decreasing list. "Ragdyer" wrote: Why do it one at a time? Why not just have your entire list re-display in random order? Say you place your original list in an out-of-the-way location, say Z1 to Z4. In Y1, enter the Rand function: =RAND() And copy down to Y4. Now, enter this formula in C1, and copy down to C4. =INDEX(Z$1:Z$4,RANK(Y1,Y$1:Y$4)) This gives you a random display of your names. Each time you hit <F9, you'll get a new random order. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Michael" wrote in message ... Thanks, that was sort of helpful, but unfortunately this does not allow a re-randomisation using only the remaining three names in the example list, and then the remaining two names etc after the first name is drawn. Also it involves manually sorting the list. "Michael" wrote: Hi All, I have a list of names and I want to randomly sort them and extract one of the sorted names in order, then randomise again, select, randomise, select etc... i.e. Say I have 4 names: Fred Betty Wilma Barney I want to randomly order the names, say it results in Fred 1st Wilma 2nd Barney 3rd Betty 4th I then want to have say the first name (Fred) copied to another cell, Say the list of names was in Cells A1:A4 so Fred would be copied to C1. Then the remainder of the list is randomised again resulting in say Betty 1st Wilma 2nd Barney 3rd Then Betty's name would be put in/copied to cell C2 Then the list is randomised again, and so on and so forth. Similar in a way to the way numbers for a lottery may be drawn, but I want to use Names, not numbers. Can Anyone Help? Thank you |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I create and extract a random order from list in Excel?
I don't understand your need to take the top name from a random ordered
list. You could just as well copy the formula from C1, and enter it wherever you want that first name to display. Then, copy the formula from C2, and enter that wherever you want the 2nd name to display, and do the same with the other formulas in C3 and C4. You'll get *no* duplicates when you hit <F9, and you'll have the 4 random names in whatever location you need them. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Michael" wrote in message ... Yes, I would agree if that is what I want, but I do not want the entire list re-displayed in random order every time. The first time I want (n) names randomised The second time I want (n-1) names where the list does not include the first name from the first randomised list. If I randomise all again, I risk getting duplicate names. The third time I want (n-2) names randomised and so on. Ultimately I will take the selcted names drawn and then match/cross reference them to two/three other lists, I have a good idea how to do the cross referencing, but am stuck on the randomising of a decreasing list. "Ragdyer" wrote: Why do it one at a time? Why not just have your entire list re-display in random order? Say you place your original list in an out-of-the-way location, say Z1 to Z4. In Y1, enter the Rand function: =RAND() And copy down to Y4. Now, enter this formula in C1, and copy down to C4. =INDEX(Z$1:Z$4,RANK(Y1,Y$1:Y$4)) This gives you a random display of your names. Each time you hit <F9, you'll get a new random order. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Michael" wrote in message ... Thanks, that was sort of helpful, but unfortunately this does not allow a re-randomisation using only the remaining three names in the example list, and then the remaining two names etc after the first name is drawn. Also it involves manually sorting the list. "Michael" wrote: Hi All, I have a list of names and I want to randomly sort them and extract one of the sorted names in order, then randomise again, select, randomise, select etc... i.e. Say I have 4 names: Fred Betty Wilma Barney I want to randomly order the names, say it results in Fred 1st Wilma 2nd Barney 3rd Betty 4th I then want to have say the first name (Fred) copied to another cell, Say the list of names was in Cells A1:A4 so Fred would be copied to C1. Then the remainder of the list is randomised again resulting in say Betty 1st Wilma 2nd Barney 3rd Then Betty's name would be put in/copied to cell C2 Then the list is randomised again, and so on and so forth. Similar in a way to the way numbers for a lottery may be drawn, but I want to use Names, not numbers. Can Anyone Help? Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract data from a random list & place in another ordered list | Excel Discussion (Misc queries) | |||
How to sort al list in random order for sampling? | Excel Discussion (Misc queries) | |||
list names in random order in Excel 2002 | Excel Discussion (Misc queries) | |||
Can I create a random order within a series of numbers in Excel? | Excel Worksheet Functions | |||
If I create a random list in Excel, does it repeat numbers? | Excel Worksheet Functions |