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