Raffle problem (Weebly related)

KW1M

New Member
Joined
Jul 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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:
raffle.jpg


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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

KW1M

New Member
Joined
Jul 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Workaround for now... with some hand editing....

Took the above and added 3 columns:
Screenshot 2020-12-01 071027.jpg

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
    Screenshot 2020-12-01 071411.jpg
  • 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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,361
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top