Raffle problem (Weebly related)

[KW1M]

I'm using Weebly to manage an online raffle for a non-profit. Unfortunately the reporting from the store is "challenged".

We allow "tickets" to be purchased in 1-3-5-10 quantities and I've done a simple-simple filtering and formula to determine the real quantity for an order. Red cells (K:Q below) are the calculated values.

My problem is how to expand such that an order for 10 tickets expands to 10 entries and the same for 3-5, ... Ideally then use a random function to pick from 1 to n from that expanded list of order numbers. Example below would have 20 individual entries for order 519220193, 10 for 139036459, etc.

Winning order number than can be looked up (vlookup?) on my master sheet where I keep all order details (name, phone, ...).

My sheet looks like this:

I've got 48 items to do, so planning to filter on SKU (1-48) and repeat...

Tried searching forum but guessing not using the right keywords to find a solution.

Thank you!
Martin

 

[KW1M]

Workaround for now... with some hand editing....

Took the above and added 3 columns:

And did some simple calculations

• Filtered table by tree #1
• Real Total: simply sum of the individual tickets from K:Q
• Running Total: First occurence (pain) = starting Real Total; then subsequent =[@[Real Total]]+S422 (S422 in this case is starting value for this tree)
• Range: T422 is hand entered as '1-2; subsequent are =CONCATENATE(S422+1,"-",S422+[@[Real Total]])
• Scroll to bottom of table
  
  
• Check that Real total (sum of column R) matches Running Total (Column S) and top of range (Column T)
• Insert three yellow cells: =RANDBETWEEN(1,Table1[[#Totals],[Real Total]])
• Gives me 3 random numbers of winners

Next steps for now...

• Lookup that random value to identify who the winner is, then lookup the order number in Column B (in my case) on another worksheet to extract Tree Name, Fname, Lname, Phone
• Will be manual for now...

After that, repeat for Autofilter Tree #2 (T002); manually set first entries and calculations should work.

Any advice appreciated if a better approach.

Thanks, Martin