Hi,

I am trying to create (what I thought was a small project) a Christmas Grab
Bag, sort of speak. A person would type in his/her name and then the computer
would randomly pick out one name of the people in the grab bag. I am trying
to use vlookup but I don't know how to have the computer only pick the name
once, with out using his/her own name. Does anyone have any ideas???? Below
is an example of what I am trying to do.

Joe
Mary
Bob
Jane
Enter your name: Joe
You Have: Bob

Is this way beyond something for excel??? It's way beyond me. I thought it
would work but I can't seem to find any functions to make this turn out.

Can someone please help?????

Here's one way...

Assume you have 20 names in the range A1:A20.

Enter this formula in B1 and copy down to B20:

=RAND()

Enter this formula in D1 and copy over to E1:

=INDEX($A:$A,MATCH(SMALL($B$1:$B$20,ROWS($1:1)+(CO LUMNS($A:A)-1)*10),$B$1:$B$20,0))

Select both D1 and E1 and copy down to row 10.

This will give you 10 random pairs of names.

Anytime the file recalculates you'll get a new "shuffle" of names. Once you
get the mix and are satisfied convert the formulas in D1:E10 to constants
and they won't shuffle anymore.

Select the range D1:E10
Goto the menu EditCopy
Then, EditPaste SpecialValuesOK

--
Biff
Microsoft Excel MVP


"Doebaby" wrote in message
...
Hi,

I am trying to create (what I thought was a small project) a Christmas
Grab
Bag, sort of speak. A person would type in his/her name and then the
computer
would randomly pick out one name of the people in the grab bag. I am
trying
to use vlookup but I don't know how to have the computer only pick the
name
once, with out using his/her own name. Does anyone have any ideas????
Below
is an example of what I am trying to do.

Joe
Mary
Bob
Jane
Enter your name: Joe
You Have: Bob

Is this way beyond something for excel??? It's way beyond me. I thought it
would work but I can't seem to find any functions to make this turn out.

Can someone please help?????

Thank You T.Valko your formula worked like a charm but is there any way of
having each person come to the computer and type in his/her own name to get
the response. We are a small office of 10 people and we can do it the old
fashion way of drawing names out of a hat but I had this brillant idea of
using the comuter instead. So while your formula works great, how do I
seperated it out so that each person can type in his/her name, in a cell and
in the next cell appear a new name (which would be the person whom they buy a
gift for)

Can it be done this way?????


"T. Valko" wrote:

Here's one way...

Assume you have 20 names in the range A1:A20.

Enter this formula in B1 and copy down to B20:

=RAND()

Enter this formula in D1 and copy over to E1:

=INDEX($A:$A,MATCH(SMALL($B$1:$B$20,ROWS($1:1)+(CO LUMNS($A:A)-1)*10),$B$1:$B$20,0))

Select both D1 and E1 and copy down to row 10.

This will give you 10 random pairs of names.

Anytime the file recalculates you'll get a new "shuffle" of names. Once you
get the mix and are satisfied convert the formulas in D1:E10 to constants
and they won't shuffle anymore.

Select the range D1:E10
Goto the menu EditCopy
Then, EditPaste SpecialValuesOK

--
Biff
Microsoft Excel MVP


"Doebaby" wrote in message
...
Hi,

I am trying to create (what I thought was a small project) a Christmas
Grab
Bag, sort of speak. A person would type in his/her name and then the
computer
would randomly pick out one name of the people in the grab bag. I am
trying
to use vlookup but I don't know how to have the computer only pick the
name
once, with out using his/her own name. Does anyone have any ideas????
Below
is an example of what I am trying to do.

Joe
Mary
Bob
Jane
Enter your name: Joe
You Have: Bob

Is this way beyond something for excel??? It's way beyond me. I thought it
would work but I can't seem to find any functions to make this turn out.

Can someone please help?????

Can it be done this way?????

Probably, but it'll be more complicated than what I suggested already. And,
doing it that way it's not truly random. If you have 10 people then only 5
of them get to enter their name. So, now you have to have some process to
see who gets to enter their name! <g

Let me see what I can come up with.


--
Biff
Microsoft Excel MVP


"Doebaby" wrote in message
...
Thank You T.Valko your formula worked like a charm but is there any way
of
having each person come to the computer and type in his/her own name to
get
the response. We are a small office of 10 people and we can do it the old
fashion way of drawing names out of a hat but I had this brillant idea of
using the comuter instead. So while your formula works great, how do I
seperated it out so that each person can type in his/her name, in a cell
and
in the next cell appear a new name (which would be the person whom they
buy a
gift for)

Can it be done this way?????


"T. Valko" wrote:

Here's one way...

Assume you have 20 names in the range A1:A20.

Enter this formula in B1 and copy down to B20:

=RAND()

Enter this formula in D1 and copy over to E1:

=INDEX($A:$A,MATCH(SMALL($B$1:$B$20,ROWS($1:1)+(CO LUMNS($A:A)-1)*10),$B$1:$B$20,0))

Select both D1 and E1 and copy down to row 10.

This will give you 10 random pairs of names.

Anytime the file recalculates you'll get a new "shuffle" of names. Once
you
get the mix and are satisfied convert the formulas in D1:E10 to constants
and they won't shuffle anymore.

Select the range D1:E10
Goto the menu EditCopy
Then, EditPaste SpecialValuesOK

--
Biff
Microsoft Excel MVP


"Doebaby" wrote in message
...
Hi,

I am trying to create (what I thought was a small project) a Christmas
Grab
Bag, sort of speak. A person would type in his/her name and then the
computer
would randomly pick out one name of the people in the grab bag. I am
trying
to use vlookup but I don't know how to have the computer only pick the
name
once, with out using his/her own name. Does anyone have any ideas????
Below
is an example of what I am trying to do.

Joe
Mary
Bob
Jane
Enter your name: Joe
You Have: Bob

Is this way beyond something for excel??? It's way beyond me. I thought
it
would work but I can't seem to find any functions to make this turn
out.

Can someone please help?????

Okay, I really appreciate the time you are taking in helping me. I will wait
and check back. Thank you again :)

"T. Valko" wrote:

Can it be done this way?????

Probably, but it'll be more complicated than what I suggested already. And,
doing it that way it's not truly random. If you have 10 people then only 5
of them get to enter their name. So, now you have to have some process to
see who gets to enter their name! <g

Let me see what I can come up with.


--
Biff
Microsoft Excel MVP


"Doebaby" wrote in message
...
Thank You T.Valko your formula worked like a charm but is there any way
of
having each person come to the computer and type in his/her own name to
get
the response. We are a small office of 10 people and we can do it the old
fashion way of drawing names out of a hat but I had this brillant idea of
using the comuter instead. So while your formula works great, how do I
seperated it out so that each person can type in his/her name, in a cell
and
in the next cell appear a new name (which would be the person whom they
buy a
gift for)

Can it be done this way?????


"T. Valko" wrote:

Here's one way...

Assume you have 20 names in the range A1:A20.

Enter this formula in B1 and copy down to B20:

=RAND()

Enter this formula in D1 and copy over to E1:

=INDEX($A:$A,MATCH(SMALL($B$1:$B$20,ROWS($1:1)+(CO LUMNS($A:A)-1)*10),$B$1:$B$20,0))

Select both D1 and E1 and copy down to row 10.

This will give you 10 random pairs of names.

Anytime the file recalculates you'll get a new "shuffle" of names. Once
you
get the mix and are satisfied convert the formulas in D1:E10 to constants
and they won't shuffle anymore.

Select the range D1:E10
Goto the menu EditCopy
Then, EditPaste SpecialValuesOK

--
Biff
Microsoft Excel MVP


"Doebaby" wrote in message
...
Hi,

I am trying to create (what I thought was a small project) a Christmas
Grab
Bag, sort of speak. A person would type in his/her name and then the
computer
would randomly pick out one name of the people in the grab bag. I am
trying
to use vlookup but I don't know how to have the computer only pick the
name
once, with out using his/her own name. Does anyone have any ideas????
Below
is an example of what I am trying to do.

Joe
Mary
Bob
Jane
Enter your name: Joe
You Have: Bob

Is this way beyond something for excel??? It's way beyond me. I thought
it
would work but I can't seem to find any functions to make this turn
out.

Can someone please help?????

If you have 10 people then only 5 of them get to enter their name.

Don't understand...

I didn't see the beginning of this this thread, and I'm not sure what the
"grab bag" is supposed to do, but if it is, e.g. buying a gift for the person
whose name you draw, A can buy a gift for B, and B can buy a gift for K. And A
could receive a give from E. It wouldn't have to be B buying a gift for A,
would it?


On Fri, 2 Nov 2007 17:26:54 -0400, "T. Valko" wrote:

Can it be done this way?????

Probably, but it'll be more complicated than what I suggested already. And,
doing it that way it's not truly random. So, now you have to have some
process to
see who gets to enter their name! <g

Let me see what I can come up with.

On Fri, 2 Nov 2007 17:26:54 -0400, "T. Valko" wrote:

Probably, but it'll be more complicated than what I suggested already. And,
doing it that way it's not truly random. If you have 10 people then only 5
of them get to enter their name. So, now you have to have some process to
see who gets to enter their name! <g

Couldn't you generate the list once, as you originally described, pairing each
person with a randomly chosen member of the group, convert the results to a
fixed list, then when the person types their name in, it looks that name up in
column A and returns the name from column B?

Hi Myrna,

Yes, I could us the orginal formula given. It does work fine but then I
would be the one knowing all who match's to who, even my own name. I didn't
realize how complicated it would be. In the end, if I don't receive anymore
ideas. I will have to do something and will probably use the orginal. I
still have some time and T.Valko expressed he might try to find something
that may work. So until then, I will wait and see. Thank you for responding.

"Myrna Larson" wrote:

On Fri, 2 Nov 2007 17:26:54 -0400, "T. Valko" wrote:

Probably, but it'll be more complicated than what I suggested already. And,
doing it that way it's not truly random. If you have 10 people then only 5
of them get to enter their name. So, now you have to have some process to
see who gets to enter their name! <g

Couldn't you generate the list once, as you originally described, pairing each
person with a randomly chosen member of the group, convert the results to a
fixed list, then when the person types their name in, it looks that name up in
column A and returns the name from column B?

Yeah, you could do that but then the results would already be predetermined.
I came up with something that's totally random but it's kind of complicated
for an average user. It uses an event macro and circular references. I'll
post it Sunday night when I get a chance. It's going to take a lot of
explanation!

--
Biff
Microsoft Excel MVP


"Myrna Larson" wrote in message
...
On Fri, 2 Nov 2007 17:26:54 -0400, "T. Valko"
wrote:

Probably, but it'll be more complicated than what I suggested already.
And,
doing it that way it's not truly random. If you have 10 people then only 5
of them get to enter their name. So, now you have to have some process to
see who gets to enter their name! <g

Couldn't you generate the list once, as you originally described, pairing
each
person with a randomly chosen member of the group, convert the results to
a
fixed list, then when the person types their name in, it looks that name
up in
column A and returns the name from column B?