Randomly pick prizes for a large list of zipcodes.

[timrxnj]

This could be a hard one.A brain twister. To start, I want to give several prizes out, randomly, to winners that are weighted by the size of a group acording to the zipcode. What did I just say??? Uhhh.
So I have 50K names and addresses. I copied the first 3 digits of each zipcode into another column.
19183 -> 191
08054 -> 080
08019 -> 080.. etc Then do a sort on this column and have them in order.
In another column I would like to have a randomly generated number with the highest number wining the prize.... Or however you would think that a random prize selection should work.
But.... There are a number of prizes so I want to take a SUBTOTAL of each of the the zipcodes 3 digit column and distribute more of the prizes in the more densly populated zips.
So if the Zip 080, has the highest subtotal percentage, then I would like to give a larger amount of the larger value prizes there.
For example, if I have prizes that I want to be applied and they would be---> 4 - $5000 / 10 - $1000 / 20 - $500 / 100 - $100 / 200 - $50 / 500 - $25 -- Then $5 to everyone else.
So for the above example I would like the higher values, the $5000 to the $500 (a total of 34 prizes) to the TOP 4 zipcodes, Then the rest just randomly chosen.
It would be great if I could do all this automaticly with a formula... But if I have to, I can assign the top 34 prizes to the top random numbers of each zipcode by hand if that is easier.and have everything else fall in line.
Anyway... I think I am over talking the question....
I know that it sounds easier to just through a prize onto any name, (which I have considered), but I have to show that it is randomly selected.
Any takers???
Any Ideas how to start????
Thanks

 

[shg]

If you just choose the awardees randomly, more prizes will intrinsically go to people in zipcodes that are more represented.

 

[FatLane]

I love these types of projects. There are a lot of different ways to do this.

My initial thought is that if your prizes were distributed randomly across all entries (without regard to zip code), the more densely populated areas (zip codes), would naturally receive more prizes. Manually altering the selection process to give more prizes and all of the top prizes to the most dense populations would be a concern if you really want to achieve true randomness. I would be concerned that someone who doesn't live in a populated area has zero chance a big prize. Of course this is okay if this is how you want to do it, but it would be a concern if other people and/or any type of auditing is involved.

I won't have time right now, but after dinner I think I will try to set up a simulation that you can test out.

 

[Rmeusanio]

Here's my suggestion

1) Add 1 new column
Col A Col B Col C Col D
zip 3digzip Name counter formula

Counter formula:
=countif(B:B,B[row]) <--- replace row with whatever row you are entering the formula into

2) Sort by column D
3) Grab the top 4 Zipcode #s
4) On a new sheet list out your prizes somewhere going down in a line
*New Sheet*
Col A Col B
$5000 pickawinner formula
$5000 etc.
$5000 etc.
$5000 etc.
$1000
$1000
...
$500

Pickawinner formula:
=Index(firstsheet!A:D,randbetween(2,countifs(B:B,"zipcode1",B:B,"zipcode2",B:B,"zipcode3",B:B,"zipcode4"),3)
^This will randomly pick a row from your first sheet and return the name of that person

5) Make sure there aren't any duplicates! (it's random so you could technically random somebody twice

 

[timrxnj]

I would be concerned that someone who doesn't live in a populated area has zero chance a big prize. Of course this is okay if this is how you want to do it, but it would be a concern if other people and/or any type of auditing is involved.

Thanks for a response. The prize ticket is in the mail and we would like them to bring the ticket in with a barcode to scan. So we kind of want them close enough to give it a try.
Still pecking away at this. Help is appreciated......

 

[timrxnj]

=Index(firstsheet!A:D,randbetween(2,countifs(B:B,"zipcode1",B:B,"zipcode2",B:B,"zipcode3",B:B,"zipcode4"),3)
^This will randomly pick a row from your first sheet and return the name of that person

Having trouble getting it to work. But still trying. Also, it seems to be a good idea and I was eager to try it, But the chance of getting duplictes is too hard to search through for 50,000 entrys or more.
Keep thinking..... Thanks