Hi Guys,
Need some help with Staff Sales and Random Picks (horizontal)
Everything I've seen so far for this kind of thing has been vertical.
List of items available for staff sale and percentage off (usually 70%)
Range: L8 to XXX8 (rightmost column, whatever letter it turns out to be) Names of staff
Range: L12 to XXX12
L13 to XXX13
etc.....
Staff place a bid in the box pertaining to the item they wish to purchase (B12 downwards)
L4 is the sum of the bids in L12 downwards
M4 is the sum of the bids in M12 downwards
etc.....
Row 5 and Row 6 (L to XXX) needs to relate to bids placed and to Column D (OBS or anything else) and to column H (Staff discounted price)
ie: Annie has placed 3 bids L12 downwards, in column D, only 1 of her bids (L12) has the code OBS and the value is taken from H as total of $300 in L5
2 of her bids come under anything else and L6 = H13 and H16 (the other 2 rows that have her bids L13 and L16)
Need to split the Items according to the number in column E (in this case 10)
so that there are now 10 rows of item 1 (E12)
Then, Random Winners chosen according to the bids. But no duplicates allowed on a per item basis. In the case of Item 1, all people will get, because there are 4 bids and 10 items.
As each person wins, that person's name has to be removed from the next round of random picks.
Where you have 3 items and 10 people, 1 person wins, name is removed from consideration for next copy of item.
Please see before and after shots below.
Number of bids and dollar value in totals (Row 4,5,6) needs to happen Before splitting items)
Any clarifications needed, please let me know.
Many thanks in advance for your help.
Al
Before:
After:
Need some help with Staff Sales and Random Picks (horizontal)
Everything I've seen so far for this kind of thing has been vertical.
List of items available for staff sale and percentage off (usually 70%)
Range: L8 to XXX8 (rightmost column, whatever letter it turns out to be) Names of staff
Range: L12 to XXX12
L13 to XXX13
etc.....
Staff place a bid in the box pertaining to the item they wish to purchase (B12 downwards)
L4 is the sum of the bids in L12 downwards
M4 is the sum of the bids in M12 downwards
etc.....
Row 5 and Row 6 (L to XXX) needs to relate to bids placed and to Column D (OBS or anything else) and to column H (Staff discounted price)
ie: Annie has placed 3 bids L12 downwards, in column D, only 1 of her bids (L12) has the code OBS and the value is taken from H as total of $300 in L5
2 of her bids come under anything else and L6 = H13 and H16 (the other 2 rows that have her bids L13 and L16)
Need to split the Items according to the number in column E (in this case 10)
so that there are now 10 rows of item 1 (E12)
Then, Random Winners chosen according to the bids. But no duplicates allowed on a per item basis. In the case of Item 1, all people will get, because there are 4 bids and 10 items.
As each person wins, that person's name has to be removed from the next round of random picks.
Where you have 3 items and 10 people, 1 person wins, name is removed from consideration for next copy of item.
Please see before and after shots below.
Number of bids and dollar value in totals (Row 4,5,6) needs to happen Before splitting items)
Any clarifications needed, please let me know.
Many thanks in advance for your help.
Al
Before:
| Excel 2010 | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | Max Number of Bids | 10 | 10 | 10 | 10 | ||||||||||||||||
| 2 | Max OBS Bid - Staff Retail Value (AU$) | $2,000.00 | $2,000.00 | $2,000.00 | $2,000.00 | ||||||||||||||||
| 3 | PLEASE COMPLETE CELLS | Max Total Bid - Staff Retail Value (AU$) | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | |||||||||||||||
| 4 | HIGHLIGHTED IN YELLOW | Your Bids - Qty | 3 | 3 | 3 | 3 | |||||||||||||||
| 5 | Your OBS Bids - Staff Retail Value (AU$) | $300.00 | $600.00 | $600.00 | $300.00 | ||||||||||||||||
| 6 | Your Total Bids - Staff Retail Value (AU$) | $1,050.00 | $1,950.00 | $1,050.00 | $900.00 | ||||||||||||||||
| 7 | LH1 - Office | LH1 - Office | LH1 - Office | LH1 - Office | |||||||||||||||||
| 8 | STORE: | A | First Name (as per payslip): | Annie | Bobbi | CharlieS | CharlieJ | ||||||||||||||
| 9 | # of Bids | Random # | Winner | ||||||||||||||||||
| 10 | |||||||||||||||||||||
| 11 | Item Code (SKU) | Description | Type | Code | Total Qty | Retail $ | Discount % | Staff Retail $ | Available Bids: 7 | Available Bids: 7 | Available Bids: 7 | Available Bids: 7 | |||||||||
| 12 | 1 | item number 1 | A | OBS | 10 | $1,000.00 | 70 | $300.00 | 4 | 1 | 1 | 1 | 1 | ||||||||
| 13 | 2 | item number 2 | B | ABC | 1 | $1,000.00 | 70 | $300.00 | 2 | 1 | 1 | ||||||||||
| 14 | 3 | item number 3 | A | DEF | 3 | $2,000.00 | 70 | $600.00 | 2 | 1 | 1 | ||||||||||
| 15 | 4 | item number 4 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 1 | 1 | 1 | ||||||||||
| 16 | 5 | item number 5 | C | ABC | 1 | $1,500.00 | 70 | $450.00 | 3 | 1 | 1 | 1 | |||||||||
| 17 | |||||||||||||||||||||
| 18 | |||||||||||||||||||||
| 19 | |||||||||||||||||||||
| 20 | |||||||||||||||||||||
| 21 | |||||||||||||||||||||
| 22 | |||||||||||||||||||||
Sheet1 | |||||||||||||||||||||
After:
| Excel 2010 | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 1 | Max Number of Bids | 10 | 10 | 10 | 10 | |||||||||||||
| 2 | Max OBS Bid - Staff Retail Value (AU$) | $2,000.00 | $2,000.00 | $2,000.00 | $2,000.00 | |||||||||||||
| 3 | PLEASE COMPLETE CELLS | Max Total Bid - Staff Retail Value (AU$) | $3,500.00 | $3,500.00 | $3,500.00 | $3,500.00 | ||||||||||||
| 4 | HIGHLIGHTED IN YELLOW | Your Bids - Qty | 3 | 3 | 3 | 3 | ||||||||||||
| 5 | Your OBS Bids - Staff Retail Value (AU$) | $300.00 | $600.00 | $600.00 | $300.00 | |||||||||||||
| 6 | Your Total Bids - Staff Retail Value (AU$) | $1,050.00 | $1,950.00 | $1,050.00 | $900.00 | |||||||||||||
| 7 | LH1 - Office | LH1 - Office | LH1 - Office | LH1 - Office | ||||||||||||||
| 8 | STORE: | A | First Name (as per payslip): | Annie | Bobbi | CharlieS | CharlieJ | |||||||||||
| 9 | # of Bids | Random # | Winner | |||||||||||||||
| 10 | ||||||||||||||||||
| 11 | Item Code (SKU) | Description | Type | Code | Total Qty | Retail $ | Discount % | Staff Retail $ | Available Bids: 7 | Available Bids: 7 | Available Bids: 7 | Available Bids: 7 | ||||||
| 12 | 1 | item number 1 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 4 | Annie | 1 | 1 | 1 | 1 | ||||
| 13 | 1 | item number 1 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 4 | CharlieJ | 1 | 1 | 1 | |||||
| 14 | 1 | item number 1 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 4 | CharlieS | 1 | 1 | ||||||
| 15 | 1 | item number 1 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 4 | Bobbi | 1 | |||||||
| 16 | 1 | item number 1 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 4 | N/A | ||||||||
| 17 | 1 | item number 1 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 4 | N/A | ||||||||
| 18 | 1 | item number 1 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 4 | N/A | ||||||||
| 19 | 1 | item number 1 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 4 | N/A | ||||||||
| 20 | 1 | item number 1 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 4 | N/A | ||||||||
| 21 | 1 | item number 1 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 4 | N/A | ||||||||
| 22 | ||||||||||||||||||
| 23 | 2 | item number 2 | B | ABC | 1 | $1,000.00 | 70 | $300.00 | 2 | Bobbi | 1 | 1 | ||||||
| 24 | ||||||||||||||||||
| 25 | 3 | item number 3 | A | DEF | 1 | $2,000.00 | 70 | $600.00 | 2 | CharlieJ | 1 | 1 | ||||||
| 26 | 3 | item number 3 | A | DEF | 1 | $2,000.00 | 70 | $600.00 | 2 | Bobbi | 1 | |||||||
| 27 | 3 | item number 3 | A | DEF | 1 | $2,000.00 | 70 | $600.00 | 2 | N/A | ||||||||
| 28 | ||||||||||||||||||
| 29 | 4 | item number 4 | A | OBS | 1 | $1,000.00 | 70 | $300.00 | 2 | CharlieS | 1 | 1 | ||||||
| 30 | ||||||||||||||||||
| 31 | 5 | item number 5 | C | ABC | 1 | $1,500.00 | 70 | $450.00 | 3 | Bobbi | 1 | 1 | 1 | |||||
| 32 | 5 | item number 5 | C | ABC | 1 | $1,500.00 | 70 | $450.00 | 3 | Annie | 1 | 1 | ||||||
| 33 | ||||||||||||||||||
| 34 | ||||||||||||||||||
| 35 | ||||||||||||||||||
Sheet1 | ||||||||||||||||||
