Hi All,
Happy new year in Advance!!!
Friends i am stuck up on automating quantity allocation sheet in excels, the logic is as follows..
We have order sheet and ETA ( Expected Time of Arrival) sheet.
Once party place order we want to share ETA against each order but there might be cases that same material will have different ETAs based on Order PO qty.
Sharing example of two material codes, 1> Material code 953681 - our 1st PO order quantity for this code is 70 which will reach in WK2001 and second lot (100Qty) will reach WK2003.
Party order details available in Order Sheet, where we want formula to some order qty and start allocating qty based on PO order Qty, now here first order qty for 953681 is 70, so formula to some order qty and allocate these qty and put respective ETA number, now for rest of the order qty formula to go to second order qty which is 100 and start allocating qty, there may be more PO order Qty or orders formula should take care of all and in case party order is greater than PO order qty formula to keep allocating till order qty is there and for rest of the qty it should say " Will share ETA shortly"
I know this is bit confusing and Thought-provoking formula but I am confident our expert team will definitely help me in developing it for sure
Thanks & Regards,
Sanket
Happy new year in Advance!!!
Friends i am stuck up on automating quantity allocation sheet in excels, the logic is as follows..
We have order sheet and ETA ( Expected Time of Arrival) sheet.
Once party place order we want to share ETA against each order but there might be cases that same material will have different ETAs based on Order PO qty.
Sharing example of two material codes, 1> Material code 953681 - our 1st PO order quantity for this code is 70 which will reach in WK2001 and second lot (100Qty) will reach WK2003.
Party order details available in Order Sheet, where we want formula to some order qty and start allocating qty based on PO order Qty, now here first order qty for 953681 is 70, so formula to some order qty and allocate these qty and put respective ETA number, now for rest of the order qty formula to go to second order qty which is 100 and start allocating qty, there may be more PO order Qty or orders formula should take care of all and in case party order is greater than PO order qty formula to keep allocating till order qty is there and for rest of the qty it should say " Will share ETA shortly"
I know this is bit confusing and Thought-provoking formula but I am confident our expert team will definitely help me in developing it for sure
Order Data.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
2 | Formula should allocate qty here | ETA should come here | |||||||||||
3 | Document Date | Sold-To Party | Name 1 | Sales Document | Purchase Order Number | Item (SD) | Material | Description | Order Quantity | Allocation Qty | ETA | ||
4 | 09-09-20 | 953721 | Pot | 10 | 10 | WK2002 | |||||||
5 | 15-09-20 | 953721 | Pot | 10 | 10 | WK2002 | |||||||
6 | 18-09-20 | 953721 | Pot | 12 | 12 | WK2002 | |||||||
7 | 21-09-20 | 953681 | Lid | 1 | 1 | WK2001 | |||||||
8 | 22-09-20 | 953721 | Pot | 2 | 2 | WK2002 | |||||||
9 | 23-09-20 | 953721 | Pot | 5 | 5 | WK2002 | |||||||
10 | 26-09-20 | 953721 | Pot | 100 | 100 | WK2002 | |||||||
11 | 28-09-20 | 953721 | Pot | 10 | 10 | WK2002 | |||||||
12 | 30-09-20 | 953721 | Pot | 10 | 10 | WK2002 | |||||||
13 | 02-10-20 | 953721 | Pot | 10 | 10 | WK2002 | |||||||
14 | 03-10-20 | 953721 | Pot | 3 | 3 | WK2002 | |||||||
15 | 03-10-20 | 953681 | Lid | 5 | 5 | WK2001 | |||||||
16 | 05-10-20 | 953681 | Lid | 15 | 15 | WK2001 | |||||||
17 | 07-10-20 | 953721 | Pot | 10 | 10 | WK2002 | |||||||
18 | 07-10-20 | 953721 | Pot | 4 | 4 | WK2002 | |||||||
19 | 07-10-20 | 953721 | Pot | 10 | 10 | WK2002 | |||||||
20 | 10-10-20 | 953721 | Pot | 10 | 10 | WK2002 | |||||||
21 | 10-10-20 | 953721 | Pot | 12 | 12 | WK2002 | |||||||
22 | 12-10-20 | 953721 | Pot | 300 | 82,218 | WK2002, WK2005 | |||||||
23 | 12-10-20 | 953721 | Pot | 10 | 10 | WK2005 | |||||||
24 | 12-10-20 | 953681 | Lid | 20 | 20 | WK2001 | |||||||
25 | 14-10-20 | 953681 | Lid | 4 | 4 | WK2001 | |||||||
26 | 14-10-20 | 953681 | Lid | 3 | 3 | WK2001 | |||||||
27 | 15-10-20 | 953681 | Lid | 10 | 10 | WK2001 | |||||||
28 | 21-10-20 | 953681 | Lid | 2 | 2 | WK2001 | |||||||
29 | 22-10-20 | 953681 | Lid | 20 | 10,10 | WK2001 , WK2003 | |||||||
30 | 26-10-20 | 953681 | Lid | 5 | 5 | WK2003 | |||||||
31 | 26-10-20 | 953681 | Lid | 10 | 10 | WK2003 | |||||||
32 | 29-10-20 | 953681 | Lid | 2 | 2 | WK2003 | |||||||
33 | 04-11-20 | 953681 | Lid | 2 | 2 | WK2003 | |||||||
34 | 07-11-20 | 953681 | Lid | 15 | 15 | WK2003 | |||||||
35 | 10-11-20 | 953681 | Lid | 13 | 13 | WK2003 | |||||||
36 | 11-11-20 | 953681 | Lid | 10 | 10 | WK2003 | |||||||
37 | 17-11-20 | 953681 | Lid | 5 | 5 | WK2003 | |||||||
Order Sheet |
Order Data.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Material | Description | Order PO Qty | ETA | Reference | ||
2 | 953721 | Pot | 300 | WK2002 | 953721_1 | ||
3 | 953681 | Lid | 70 | WK2001 | 953681_1 | ||
4 | 953721 | Pot | 300 | WK2005 | 953721_2 | ||
5 | 953681 | Lid | 100 | WK2003 | 953681_2 | ||
ETA Sheet |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E5 | E2 | =A2&"_"&COUNTIF($A$2:A2,A2) |
Thanks & Regards,
Sanket