Lucky draw

[Kitri]

Hi there,
I was assigned a task about lucky draw with conditions and arrangement as follow :
1. There will be 100 employees and 5 prizes
2. Prize will be split into 5 categories :
a. 1st draw : 50 employees with nominal prize ($100/each)
b. 2nd draw : 25 employees with nominal prize ($200/each)
c. 3rd draw : 15 employees with nominal prize ($300/each)
d. 4th draw : 8 employees with nominal prize ($400/each)
e. 5th draw : 2 employees with nominal prize ($500/each)

Need your kind advise on this task since I’m quite new on learning vba.

Thank you in advance

 

[Eric W]

Welcome to the MrExcel forum!

A while back, I wrote up a macro that does pretty much exactly what you're looking for. Check out this thread:

Random lucky draw (Name and prize with some conditions)

Hi there, I was assigned a task about an lucky draw event in our department as Xmas is coming. We have 42 employees and we have prepare 42 prizes as follows: Cash Prize Total Number $500 2 $300 3 $200 5 $100 32 We also have the name list of 42 employees. The arrangement: 1. We may...

www.mrexcel.com

See what you think and we can adapt it as needed.

 

[johnnyL]

You didn't give much info about what you are looking for so here are some formulas to generate a random number for each drawing:

Book1
	A	B	C	D	E	F
1	Random number between 1 & 50	Random number between 1 & 25	Random number between 1 & 15	Random number between 1 & 8	Random number between 1 & 2
2	$100	$200	$300	$400	$500
3	30	20	5	5	2
4
Sheet2

Cell Formulas
Range	Formula
A3	A3	= RANDBETWEEN(1, 50)
B3	B3	= RANDBETWEEN(1, 25)
C3	C3	= RANDBETWEEN(1, 15)
D3	D3	= RANDBETWEEN(1, 8)
E3	E3	= RANDBETWEEN(1, 2)

The F9 key will recalculate all columns.

 

[Kitri]

Welcome to the MrExcel forum!

A while back, I wrote up a macro that does pretty much exactly what you're looking for. Check out this thread:

Random lucky draw (Name and prize with some conditions)

Hi there, I was assigned a task about an lucky draw event in our department as Xmas is coming. We have 42 employees and we have prepare 42 prizes as follows: Cash Prize Total Number $500 2 $300 3 $200 5 $100 32 We also have the name list of 42 employees. The arrangement: 1. We may...

www.mrexcel.com

See what you think and we can adapt it as needed.

Hi,
Thank you for replying my questions.
I tried to apply this macro,it works, but the problems are :
1. When we draw the name, its only appear 1 winner, while in my task the winner will divided into 5 categories as I mentioned previously. First, it will come out with 50 names, second : 25 names, third : 15 names, forth : 8 names, and the last : 2 names.
2. We don’t need to draw the prize since the prizes are already mentioned.

Please help. Thankyou

 

[Kitri]

H

You didn't give much info about what you are looking for so here are some formulas to generate a random number for each drawing:

Book1
	A	B	C	D	E	F
1	Random number between 1 & 50	Random number between 1 & 25	Random number between 1 & 15	Random number between 1 & 8	Random number between 1 & 2
2	$100	$200	$300	$400	$500
3	30	20	5	5	2
4
Sheet2

Cell Formulas
Range	Formula
A3	A3	= RANDBETWEEN(1, 50)
B3	B3	= RANDBETWEEN(1, 25)
C3	C3	= RANDBETWEEN(1, 15)
D3	D3	= RANDBETWEEN(1, 8)
E3	E3	= RANDBETWEEN(1, 2)

The F9 key will recalculate all columns.

Hi,
Thankyou for answering my questions.
Sorry for the simple information.
I tried to use the formula, but it will show up at one time, while I need to split it into 5 categories like i mentioned previously.
So when we press the button, it will generate 50 names randomly. Then next round when we press the button it will generate 25 names, then 15 names, 8 and the last 2 names.
The winner that already mentioned should not be appeared on the next draw.

Hope above explaination is clear.
Please advise. Thankyou

 

[Eric W]

I'm afraid I don't understand what you're doing then. You say the first draw has 50 employees with 50 prizes of $100? Why draw at all? Just give them all $100. Same with the other draws.

The way my macro works is that you click "Draw Name" and a random name is drawn from the remaining people. Then you click "Draw Amount", and the amount is chosen for that person. Repeat as many times as you need. Or to spice things up, you can switch it up and draw the amount first. The idea behind the macro is that everyone is at a party or group meeting, and the one-at-a-time drawing creates anticipation and fun.

If you just want a list of prizes that you'll distribute at a later time, then all we need to do is create a list of names, a list of prizes, then randomly sort the list of prizes. Let us know if that's what you want.

 

[johnnyL]

@Kitri I think I am still confused to your ask. Let me see if I can rephrase what I think you are asking.

1) You have 100 employees that are eligible for 5 drawings until they have won a drawing and then they are excluded from any additional drawings.
2) Your first drawing will be for $100 amongst 50 randomly generated employee names.
3) Here is where the confusion comes in ... you stated $100/each. What does that mean? There are multiple $100 prizes for that drawing? If so, how many?
4) Second drawing will be for $200 amongst 25 randomly generated employee names, of the 100 total employees, that have not previously won a prize.
5) Pertains to #3 except different Dollar amount
etc
etc
etc

 

[Kitri]

I'm afraid I don't understand what you're doing then. You say the first draw has 50 employees with 50 prizes of $100? Why draw at all? Just give them all $100. Same with the other draws.

The way my macro works is that you click "Draw Name" and a random name is drawn from the remaining people. Then you click "Draw Amount", and the amount is chosen for that person. Repeat as many times as you need. Or to spice things up, you can switch it up and draw the amount first. The idea behind the macro is that everyone is at a party or group meeting, and the one-at-a-time drawing creates anticipation and fun.

If you just want a list of prizes that you'll distribute at a later time, then all we need to do is create a list of names, a list of prizes, then randomly sort the list of prizes. Let us know if that's what you want.

Apologize for makes you confuse.
Lets we forget about the prize since we don’t need to draw the prize.

Yes,your “draw name” macro works, but can we generate 50 names at one time, then next draw 25 names for remaining people, etc until the last draw only 2 names will be appeared.
the names should not be duplicated with previous one.

Hope my explaination is clear.
Thank you

 

[Kitri]

@Kitri I think I am still confused to your ask. Let me see if I can rephrase what I think you are asking.

1) You have 100 employees that are eligible for 5 drawings until they have won a drawing and then they are excluded from any additional drawings.
2) Your first drawing will be for $100 amongst 50 randomly generated employee names.
3) Here is where the confusion comes in ... you stated $100/each. What does that mean? There are multiple $100 prizes for that drawing? If so, how many?
4) Second drawing will be for $200 amongst 25 randomly generated employee names, of the 100 total employees, that have not previously won a prize.
5) Pertains to #3 except different Dollar amount
etc
etc
etc

Yes.that exactly what I asked. Exactly what you are rephrase on point 1,2,4,5 and just forget about “$100/each”
3) $100/each —> i mean 50 employees will get $100 per employee.

Apologize for makes you confuse.

 

[johnnyL]

Aha! So let me try Rephrase V2.

1) You have 100 employees.
2) You want to generate a random list of 50 of those employee names. They will all receive $100.
3) You then want to generate a random list of 25 employees of the 50 remaining employees. They will all receive $200.
4) You then want to generate a random list of 15 employees of the 25 remaining employees. They will all receive $300.
5) You then want to generate a random list of 8 employees of the 10 remaining employees. They will all receive $400.
6) Last drawing is pointless because there are only 2 employees left that will receive $500 each.

That all correct?

One more question. Where is your list of employee names? What sheet name and what address range?