Generating a Random Number Table, where all of the numbers within the table are not used

[robreich]

I received some help on this Forum last October from Rick Rothstein creating a Random Number generator table, assuming 500 numbers (1 - 500). This Table is being used for a Reverse Raffle at a Charity Function in Ohio. To execute the macro, I am prompted to enter the first number in the Table, the last number in the Table, and how many numbers I want in each row of the Random Number Table. A question has come up as we approach our first use of the macro this coming October. What if all of the 500 tickets are not sold? They are being sold by multiple people that are members of the organization that is sponsoring the Charity Function. Everyone is given 10 tickets to sell. In a lot of cases, they sell only a portion of them and turn the unsold ones back into the Chairman. He then tries to use them as he gets more requests for tickets, but by the Evening of the fund raiser, there are always a number of tickets that have not been sold. Obviously, if the last ticket sold is less than 500, we simply enter that as the LAST NUMBER. However, is there a way to deal with the issue if tickets within the Table have not been sold, i.e. Tickets Number 275 thru 280 do not sell, 340 thru 345 do not sell, etc.? Right now, we are assuming that their are no unsold tickets in the Table of Numbers. One of the reasons of trying to address this is that every 10th ticket drawn wins a $25.00 Gift Card. If we simply put the unsold tickets up on the board and remove them as they are "drawn", then the "unsold tickets" mess up the Gift Cards.


I was thinking along the lines of building a Table Range that contains all of the SOLD Tickets only (leaving a blank cell where a ticket has not sold). Then, having the macro pull the random numbers from this Table Range. Not sure if that makes sense or it is doable, but if someone could help me with this, it would be greatly appreciated.

I am working in EXCEL 365 and my Operation System is Windows 10.

If someone needs additional information or has questions, please post them here. I will be checking the forum daily.

Thank you in advance for any help you can give me.

Bob Reichert

 

[robreich]

OK. Now I understand. I think when I first tested it, I had that problem of a series turning into a date and I did as you suggested and it worked when I tested it with a series of numbers. I will edit the macro and remember to enter a series as 'xx-xx.

Thanks.

 

[Peter_SSs]

I will ... remember to enter a series as 'xx-xx.

It won't matter if you do that for all series, but you only need to do it for ones that could be interpreted as dates. For example, you wouldn't need to do it for 35-52