Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 791
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 791
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 791
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
extract data from a random list & place in another ordered list sean8690 Excel Discussion (Misc queries) 1 January 2nd 07 07:06 PM
How to sort al list in random order for sampling? NJTom Excel Discussion (Misc queries) 1 September 21st 06 10:50 PM
list names in random order in Excel 2002 John Murf Excel Discussion (Misc queries) 2 February 25th 06 02:58 AM
Can I create a random order within a series of numbers in Excel? Dimtrax Excel Worksheet Functions 2 November 9th 05 04:40 PM
If I create a random list in Excel, does it repeat numbers? Kelly Excel Worksheet Functions 2 March 30th 05 04:05 AM


All times are GMT +1. The time now is 12:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"