Random selection

[Janly]

How can I set a random selection based on a set number of records in
Excel, without repeating the selected record?

I have a column with a list of individual names and another column with
list of countries. I want to randomly select a country in the 2nd
column and assign it to the person's name in the 1st column. However,
once the first selection is made I cannot use that country name again
for the second selection, and so on.

An example that describes what I want to do is when we select names
from a hat for gift exchange during holidays. It is a set of specific
names, and they will not repeat, however, it is randomly selected.

Any help would be greatly appreciated.

Thanks.

 

[Dave R.]

What you can do is in a 3rd column enter RAND() and copy it to all rows.

Then select the countries and the random numbers, and sort by the random
numbers.

Then you'll have the names, and countries next to them in random order. You
can just match the name/country by row then.

 

[Tushar Mehta]

For additional options to the one already provided, check the Excel |
Tutorials | 'Random Selection' page of my web site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Janly]

Thank you both. I tried both methods and they work great. I do hav
one more problem associated with it, which I forgot to mention. Som
of the staff cannot have a specific country. What I mean is, th
selection cannot be the country where the person originated from.

e.g.

NAME COUNTR
JohnD Spain
DoYou France
WillDo Italy

They are currently sorted by the country that they came from. I nee
to do a random selection from the 3 above countries, without John
getting Spain, etc.

I was thinking that I might have to add another column for the countr
again to do some type of comparison, so that the random can be (in thi
case) anything but Spain.

Disgracefully, it is somewhat of a long list which is why I wanted t
see if it could be done with some type of formula. Otherwise, I woul
have to do it manually. Or use the options that Tushar Mehta and Dav
R. very kindly provided and manually verify it, which means variou
tries of random selection.

Thanks Tushar Mehta and Dave R. for your help. BTW, Tushar, thanks fo
the link to your homepage. Very valuable

 

[Dave R.]

Janly, I am fairly sure that the process would have to be done with VBA to
get it done perfectly the first time, so would take someone with little
programming experience like me some time to do.

However, this will probably work for you:

Create 2 lists of numbers 1 to as many employees/countries as you have (do
you have the same #s of each?)- seperate the lists by 1 column. For example,
if you have 100 employees and 100 countries, then your numbered lists are 1
to 100. one list in column A and one list in column C. These numbers will
represent the countries and names.

Copy and paste these same numbers next to your current list of
names/countries (so you'll have 3 columns total). This is going to be the
data you actually get the list from, so keep it in this order. this is your
ORIGINAL list.

in B1, enter RAND() and copy that down to B100

Now, enter this formula in F1 -
=SUMPRODUCT(IF((A1:A100)=(C1:C100),1)*1) -- and instead of pressing
enter, press CTRL SHIFT ENTER at the same time. the formula will then appear
with {} around it.

This shows you the number of matches between your lists. At this point, it
should be 100.

Now, select list 2 (country list) AND the random number column. Then sort by
the random numbers. Look at the formula; if it shows 0 -- you're set since
now for each person (designated by a number 1-100) there is a country code
(1-100) different from where they started. If it's not 0 the first time,
press CTRL Y a few times until it is.

Now.. copy the newly arranged numbers in column C (the #s from 1-100) next
to the countries in your ORIGINAL list. There are 100 countries now, with
out of order numbers between 1 and 100 next to them. Now, select the the
countries and the out of order numbers, and sort on the numbers. Now they'll
be in ascending order, and none of the names originally matched with the
countries will match the same way.

Hope this helps (and that you're still following the thread!).