Random name from a list?

[Terry Pinnell]

I have a list of 20 names in col A. In B1 I want to display a random
name from this list. What is the easiest way to do that please? An
(admittedly brief) study of VLOOKUP in Help for Excel 2000 left me a
bit confused.

 

[Sheeloo]

Enter the following in B1
=INDIRECT("A" & ROUND(RAND()*20,0)+1)

Help:
=ROUND(RAND()*20,0) will give you a random number between 1 and 19
Add one to it and prefix the result with A and you will get A1-A20. Indirect
will return the value in A1-A20...

 

[Max]

Another play ..
Names in A1:A20
In B1: =RAND()
Copy down to B20
Then in C1: =INDEX($A$1:$A$20,RANK(B1,$B$1:$B$20))
will return a random name from the source list. Press F9 to regenerate.
Just copy C1 down as required if you need to generate more random picks
(Picks will not repeat). Or simply copy C1 down all the way to C20 to return
a full random scramble of the entire source list.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600, Files:362, Subscribers:60
xdemechanik

 

[James Silverton]

Max wrote on Tue, 7 Oct 2008 16:48:42 +0800:

Another play ..
Names in A1:A20
In B1: =RAND()
Copy down to B20
Then in C1: =INDEX($A$1:$A$20,RANK(B1,$B$1:$B$20))
will return a random name from the source list. Press F9 to
regenerate. Just copy C1 down as required if you need to
generate more random picks (Picks will not repeat). Or simply copy C1
down all the way to C20 to return a full random
scramble of the entire source list. --
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600, Files:362, Subscribers:60
xdemechanik

If it's a thing that you want to do from time to time, you could use the
helper column method, say in B, with the names and addresses in A.
Elsewhere, perhaps in C1, you might have "The winner is", and =A1 in D1.
You could even record a macro to get a new winner without going thro'
all the motions.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Terry Pinnell]

Max wrote on Tue, 7 Oct 2008 16:48:42 +0800:


If it's a thing that you want to do from time to time, you could use the
helper column method, say in B, with the names and addresses in A.
Elsewhere, perhaps in C1, you might have "The winner is", and =A1 in D1.
You could even record a macro to get a new winner without going thro'
all the motions.

Thanks all, much appreciate the concise suggestions.

 

[Max]

Thanks all, much appreciate the concise suggestions.

Welcome. What is important is that it works for you,
and you understand what's happening
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---