Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Desperately Need Help. Technic To Return Date
Question 1. I need a formula to return the furthest delivery date per a
particular part. Eg. I want the aerliest delivery date for Screw AA, the answer is 1-Jan-07. Quastion 2 : This is the one that makes me crazy thinking better ways to solve. I want to have a formula that can automatically return the date of a particular product will out of stock based on customers order. Please refer to table below. I have 35,000 Screw AA left in finish good store. Based on actual customers PO, I can support customers demand until 4-Jan-07, stock is not enough for 3-Feb-07 delivery . Is there any lookup or formula that can tell me the date WITHOUT have to sorting data (in sequence order)? My current method of doing : I will sort data by columm A after input all new PO information. Then, in columm B, I use Vlookup formula to find data from another worksheet. Then, in columm E is Stock-PO Qty, drag from the top row to bottom row of each Part Name. Then, create formula in columm F : (=MIN(IF((A$2:A$10000=A2)*(E$10:E$10000<=0),C$2:C$ 10000))) enter with CSE. Then, copy this formula to every top row of each item. The above mention formula is workable. but it really takes a lot of time to accomplish. Input PO data, Sort Data, Create lookup, create Balance qty formula, and create formula in columm F and finally drag/copy each item by item. IS there any better ways to accomplish this? ESPECIALLY Without have to SORT and COPY and PASTE, DRAG manually item by item??? Is there any formula to return the date even without columm F? Columm 'A' contains more than 1,500 different part name. A B C D E F 1 Part name stock Delivery date PO QTY Balance Qty Out Date 2 Screw AA 35,000 1-Jan-07 22,000 3-Feb-07 3 Screw BB 35,000 2-Jan-07 18,000 2-Feb-07 4 Screw CC 35,000 3-Jan-07 20,000 3-Feb-07 5 Screw AA 4-Jan-07 10,000 6 Screw BB 5-Jan-07 10,000 7 Screw CC 6-Jan-07 10,000 8 Screw AA 3-Feb-07 12,000 9 Screw BB 2-Feb-07 10,000 10 Screw CC 3-Feb-07 10,000 Desperately need help. Thanks. Sara |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Desperately Need Help. Technic To Return Date
Hi Sara
I feel that we only have part of the picture here, as I don't see how stocks ever get increased to meet orders, unless you enter deliveries as negative purchase orders. However, on the basis of the information provided. do the following. Insert a new row 1, pushing your headers to row 2 In all cases, adjust the total length of the range to suit your data In B1 =VLOOKUP(A1,$A$3:$B$100,2,0) in E1 =INDEX(E3:E100,MATCH(F1,F3:F100,0)) in F1 enter the array formula with CSE {=MIN(IF(F3:F100TODAY(),F3:F100,""))} In E3 =$B$1-SUMIF($A$3:$A3,A$1,$D$3:$D3) in F3 =IF(E3<0,C3,"") copy E3:F3 down as far as required, having adjusted the range Now, if you enter ScrewAA in cell 1, it will show the relevant date in F1 and the negative quantity that would be created by that PO in E1. Change values in A1 to bring up data for other products. -- Regards Roger Govier "Sara" wrote in message ... Question 1. I need a formula to return the furthest delivery date per a particular part. Eg. I want the aerliest delivery date for Screw AA, the answer is 1-Jan-07. Quastion 2 : This is the one that makes me crazy thinking better ways to solve. I want to have a formula that can automatically return the date of a particular product will out of stock based on customer's order. Please refer to table below. I have 35,000 Screw AA left in finish good store. Based on actual customer's PO, I can support customer's demand until 4-Jan-07, stock is not enough for 3-Feb-07 delivery . Is there any lookup or formula that can tell me the date WITHOUT have to sorting data (in sequence order)? My current method of doing : I will sort data by columm A after input all new PO information. Then, in columm B, I use Vlookup formula to find data from another worksheet. Then, in columm E is Stock-PO Qty, drag from the top row to bottom row of each Part Name. Then, create formula in columm F : (=MIN(IF((A$2:A$10000=A2)*(E$10:E$10000<=0),C$2:C$ 10000))) enter with CSE. Then, copy this formula to every top row of each item. The above mention formula is workable. but it really takes a lot of time to accomplish. Input PO data, Sort Data, Create lookup, create Balance qty formula, and create formula in columm F and finally drag/copy each item by item. IS there any better ways to accomplish this? ESPECIALLY Without have to SORT and COPY and PASTE, DRAG manually item by item??? Is there any formula to return the date even without columm F? Columm 'A' contains more than 1,500 different part name. A B C D E F 1 Part name stock Delivery date PO QTY Balance Qty Out Date 2 Screw AA 35,000 1-Jan-07 22,000 3-Feb-07 3 Screw BB 35,000 2-Jan-07 18,000 2-Feb-07 4 Screw CC 35,000 3-Jan-07 20,000 3-Feb-07 5 Screw AA 4-Jan-07 10,000 6 Screw BB 5-Jan-07 10,000 7 Screw CC 6-Jan-07 10,000 8 Screw AA 3-Feb-07 12,000 9 Screw BB 2-Feb-07 10,000 10 Screw CC 3-Feb-07 10,000 Desperately need help. Thanks. Sara |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Desperately Need Help. Technic To Return Date
Hi,
Why do you need to sort your data? Try this in column E: =SUMPRODUCT(($C$2:$C$100<=C2)*($A$2:$A$100=A2),$B$ 2:$B$100)-SUMPRODUCT(($C$2:$C$100<=C2)*($A$2:$A$100=A2),$D$2 :$D$100) this will give you the remaining stock qty on the date in column C...no need to sort! in column F: =IF(COUNTIF($A$2:A2,A2)=1,IF(MIN(IF(($A$2:$A$100=A 2)*($E$2:$E$100<0),$C$2:$C$100))=0,"OK",MIN(IF(($A $2:$A$100=A2)*($E$2:$E$100<0),$C$2:$C$100))),"") this will look at the first instance of a product in column A and give you the out of stock date or the "OK" status......no need to sort! Or if you have a list of all the product in a column (say column G) then you can use this instead of column F: =IF(MIN(IF(($A$2:$A$100=G2)*($E$2:$E$100<0),$C$2:$ C$100))=0,"OK",MIN(IF(($A$2:$A$100=G2)*($E$2:$E$10 0<0),$C$2:$C$100))) this will look at the product in the list and either give the out of stock date of the "OK" status. the last 2 are array formulas so enter using Ctrl+Shift+Enter.......adjust the ranges to suit then drag down as needed....no need to sort just enter the data at the end. Hope this helps! Jean-Guy "Sara" wrote: Question 1. I need a formula to return the furthest delivery date per a particular part. Eg. I want the aerliest delivery date for Screw AA, the answer is 1-Jan-07. Quastion 2 : This is the one that makes me crazy thinking better ways to solve. I want to have a formula that can automatically return the date of a particular product will out of stock based on customers order. Please refer to table below. I have 35,000 Screw AA left in finish good store. Based on actual customers PO, I can support customers demand until 4-Jan-07, stock is not enough for 3-Feb-07 delivery . Is there any lookup or formula that can tell me the date WITHOUT have to sorting data (in sequence order)? My current method of doing : I will sort data by columm A after input all new PO information. Then, in columm B, I use Vlookup formula to find data from another worksheet. Then, in columm E is Stock-PO Qty, drag from the top row to bottom row of each Part Name. Then, create formula in columm F : (=MIN(IF((A$2:A$10000=A2)*(E$10:E$10000<=0),C$2:C$ 10000))) enter with CSE. Then, copy this formula to every top row of each item. The above mention formula is workable. but it really takes a lot of time to accomplish. Input PO data, Sort Data, Create lookup, create Balance qty formula, and create formula in columm F and finally drag/copy each item by item. IS there any better ways to accomplish this? ESPECIALLY Without have to SORT and COPY and PASTE, DRAG manually item by item??? Is there any formula to return the date even without columm F? Columm 'A' contains more than 1,500 different part name. A B C D E F 1 Part name stock Delivery date PO QTY Balance Qty Out Date 2 Screw AA 35,000 1-Jan-07 22,000 3-Feb-07 3 Screw BB 35,000 2-Jan-07 18,000 2-Feb-07 4 Screw CC 35,000 3-Jan-07 20,000 3-Feb-07 5 Screw AA 4-Jan-07 10,000 6 Screw BB 5-Jan-07 10,000 7 Screw CC 6-Jan-07 10,000 8 Screw AA 3-Feb-07 12,000 9 Screw BB 2-Feb-07 10,000 10 Screw CC 3-Feb-07 10,000 Desperately need help. Thanks. Sara |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Desperately Need Help. Technic To Return Date
THANKS! It really works! Appreciate your helps!! Thanks to those who have
tried to help me. Wish you a good day. Sara "pinmaster" wrote: Hi, Why do you need to sort your data? Try this in column E: =SUMPRODUCT(($C$2:$C$100<=C2)*($A$2:$A$100=A2),$B$ 2:$B$100)-SUMPRODUCT(($C$2:$C$100<=C2)*($A$2:$A$100=A2),$D$2 :$D$100) this will give you the remaining stock qty on the date in column C...no need to sort! in column F: =IF(COUNTIF($A$2:A2,A2)=1,IF(MIN(IF(($A$2:$A$100=A 2)*($E$2:$E$100<0),$C$2:$C$100))=0,"OK",MIN(IF(($A $2:$A$100=A2)*($E$2:$E$100<0),$C$2:$C$100))),"") this will look at the first instance of a product in column A and give you the out of stock date or the "OK" status......no need to sort! Or if you have a list of all the product in a column (say column G) then you can use this instead of column F: =IF(MIN(IF(($A$2:$A$100=G2)*($E$2:$E$100<0),$C$2:$ C$100))=0,"OK",MIN(IF(($A$2:$A$100=G2)*($E$2:$E$10 0<0),$C$2:$C$100))) this will look at the product in the list and either give the out of stock date of the "OK" status. the last 2 are array formulas so enter using Ctrl+Shift+Enter.......adjust the ranges to suit then drag down as needed....no need to sort just enter the data at the end. Hope this helps! Jean-Guy "Sara" wrote: Question 1. I need a formula to return the furthest delivery date per a particular part. Eg. I want the aerliest delivery date for Screw AA, the answer is 1-Jan-07. Quastion 2 : This is the one that makes me crazy thinking better ways to solve. I want to have a formula that can automatically return the date of a particular product will out of stock based on customers order. Please refer to table below. I have 35,000 Screw AA left in finish good store. Based on actual customers PO, I can support customers demand until 4-Jan-07, stock is not enough for 3-Feb-07 delivery . Is there any lookup or formula that can tell me the date WITHOUT have to sorting data (in sequence order)? My current method of doing : I will sort data by columm A after input all new PO information. Then, in columm B, I use Vlookup formula to find data from another worksheet. Then, in columm E is Stock-PO Qty, drag from the top row to bottom row of each Part Name. Then, create formula in columm F : (=MIN(IF((A$2:A$10000=A2)*(E$10:E$10000<=0),C$2:C$ 10000))) enter with CSE. Then, copy this formula to every top row of each item. The above mention formula is workable. but it really takes a lot of time to accomplish. Input PO data, Sort Data, Create lookup, create Balance qty formula, and create formula in columm F and finally drag/copy each item by item. IS there any better ways to accomplish this? ESPECIALLY Without have to SORT and COPY and PASTE, DRAG manually item by item??? Is there any formula to return the date even without columm F? Columm 'A' contains more than 1,500 different part name. A B C D E F 1 Part name stock Delivery date PO QTY Balance Qty Out Date 2 Screw AA 35,000 1-Jan-07 22,000 3-Feb-07 3 Screw BB 35,000 2-Jan-07 18,000 2-Feb-07 4 Screw CC 35,000 3-Jan-07 20,000 3-Feb-07 5 Screw AA 4-Jan-07 10,000 6 Screw BB 5-Jan-07 10,000 7 Screw CC 6-Jan-07 10,000 8 Screw AA 3-Feb-07 12,000 9 Screw BB 2-Feb-07 10,000 10 Screw CC 3-Feb-07 10,000 Desperately need help. Thanks. Sara |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Desperately Need Help. Technic To Return Date
You're Welcome....glad I could help!
You also wanted the furthest date the stock is good for right? For this can use the same formula that gives the out of stock date but instead of MIN use MAX with =0 as the argument. Regards! Jean-Guy "Sara" wrote: THANKS! It really works! Appreciate your helps!! Thanks to those who have tried to help me. Wish you a good day. Sara "pinmaster" wrote: Hi, Why do you need to sort your data? Try this in column E: =SUMPRODUCT(($C$2:$C$100<=C2)*($A$2:$A$100=A2),$B$ 2:$B$100)-SUMPRODUCT(($C$2:$C$100<=C2)*($A$2:$A$100=A2),$D$2 :$D$100) this will give you the remaining stock qty on the date in column C...no need to sort! in column F: =IF(COUNTIF($A$2:A2,A2)=1,IF(MIN(IF(($A$2:$A$100=A 2)*($E$2:$E$100<0),$C$2:$C$100))=0,"OK",MIN(IF(($A $2:$A$100=A2)*($E$2:$E$100<0),$C$2:$C$100))),"") this will look at the first instance of a product in column A and give you the out of stock date or the "OK" status......no need to sort! Or if you have a list of all the product in a column (say column G) then you can use this instead of column F: =IF(MIN(IF(($A$2:$A$100=G2)*($E$2:$E$100<0),$C$2:$ C$100))=0,"OK",MIN(IF(($A$2:$A$100=G2)*($E$2:$E$10 0<0),$C$2:$C$100))) this will look at the product in the list and either give the out of stock date of the "OK" status. the last 2 are array formulas so enter using Ctrl+Shift+Enter.......adjust the ranges to suit then drag down as needed....no need to sort just enter the data at the end. Hope this helps! Jean-Guy "Sara" wrote: Question 1. I need a formula to return the furthest delivery date per a particular part. Eg. I want the aerliest delivery date for Screw AA, the answer is 1-Jan-07. Quastion 2 : This is the one that makes me crazy thinking better ways to solve. I want to have a formula that can automatically return the date of a particular product will out of stock based on customers order. Please refer to table below. I have 35,000 Screw AA left in finish good store. Based on actual customers PO, I can support customers demand until 4-Jan-07, stock is not enough for 3-Feb-07 delivery . Is there any lookup or formula that can tell me the date WITHOUT have to sorting data (in sequence order)? My current method of doing : I will sort data by columm A after input all new PO information. Then, in columm B, I use Vlookup formula to find data from another worksheet. Then, in columm E is Stock-PO Qty, drag from the top row to bottom row of each Part Name. Then, create formula in columm F : (=MIN(IF((A$2:A$10000=A2)*(E$10:E$10000<=0),C$2:C$ 10000))) enter with CSE. Then, copy this formula to every top row of each item. The above mention formula is workable. but it really takes a lot of time to accomplish. Input PO data, Sort Data, Create lookup, create Balance qty formula, and create formula in columm F and finally drag/copy each item by item. IS there any better ways to accomplish this? ESPECIALLY Without have to SORT and COPY and PASTE, DRAG manually item by item??? Is there any formula to return the date even without columm F? Columm 'A' contains more than 1,500 different part name. A B C D E F 1 Part name stock Delivery date PO QTY Balance Qty Out Date 2 Screw AA 35,000 1-Jan-07 22,000 3-Feb-07 3 Screw BB 35,000 2-Jan-07 18,000 2-Feb-07 4 Screw CC 35,000 3-Jan-07 20,000 3-Feb-07 5 Screw AA 4-Jan-07 10,000 6 Screw BB 5-Jan-07 10,000 7 Screw CC 6-Jan-07 10,000 8 Screw AA 3-Feb-07 12,000 9 Screw BB 2-Feb-07 10,000 10 Screw CC 3-Feb-07 10,000 Desperately need help. Thanks. Sara |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return tomorrow's date. | New Users to Excel | |||
Using TODAY function to return a date as text | Excel Discussion (Misc queries) | |||
from date return week date range | Excel Worksheet Functions | |||
VLOOKUP return text not date | Excel Worksheet Functions | |||
calc constant date from variable date & return with ability to rn. | Excel Worksheet Functions |