How to pull the date an item becomes first negative based on item number

[Jhammon2]

Hello,

I am trying to find a formula to pull the first date an item becomes negative in a set of data. I've been trying index and match together but cant seem to get it to work. I know that using the min function to find the earliest date will but the same date on all the repeated item numbers and that's ok. What we are working on is a report to tell us when to move in a purchase order for that item if the due date is larger than the date the item goes negative. Below is an example of the data set I am working with. With Col A being the item number, Col I being the field I want the date returned, and Col P being the range where the OH qty would turn negative, and COL J being the qty at the first negative date.

I would be looking for COL I for the first item 01-31951-000 to show 05/24/17 in COL I and the qty 63 to be in COL J.

Thank you, please let me know if you need any other information. I would really appreciate any help with this, I have been working on this for a few days now and I am stuck.

Textbox18	SUPPLYQTY1	DEMANDQTY1	MRPTYPE	ORDERNUMBER1	Item	RELEASEDATE_I	DUEDATE	Move in Date	Move in Qty	SUPPLYQTY	DEMANDQTY	STSDESCR	MOVEIN	MOVEOUT	CANCELLED	PAB
01-31951-000	2,737	2,550	IV	MAIN		1/1/1900	7/9/2016			387	0	On Hand				387
01-31951-000	2,737	2,550	SOP	4003777		1/1/1900	5/19/2017			0	150	Order				237
01-31951-000	2,737	2,550	SOP	4010189		1/1/1900	5/24/2017			0	150	Order				87
01-31951-000	2,737	2,550	SOP	4010245		1/1/1900	5/24/2017			0	150	Order				-63
01-31951-000	2,737	2,550	POP	PO193294		3/9/2017	5/26/2017			850	0	Firm	X			787

<colgroup><col width="135" style="width: 101pt;"> <col width="83" style="width: 62pt;"> <col width="95" style="width: 71pt;"> <col width="64" style="width: 48pt;"> <col width="135" style="width: 101pt;"> <col width="170" style="width: 128pt;"> <col width="102" style="width: 77pt;"> <col width="75" style="width: 56pt;"> <col width="89" style="width: 67pt;" span="2"> <col width="76" style="width: 57pt;"> <col width="87" style="width: 65pt;"> <col width="160" style="width: 120pt;"> <col width="59" style="width: 44pt;"> <col width="71" style="width: 53pt;"> <col width="77" style="width: 58pt;"> <col width="51" style="width: 38pt;"> <tbody> </tbody>

 

[Nishant94]

Is this what you want?

Here I am assuming that you have more than one type of item in col A.

Unknown
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Textbox18	SUPPLYQTY1	DEMANDQTY1	MRPTYPE	ORDERNUMBER1	Item	RELEASEDATE_I	DUEDATE	Move in Date	Move in Qty	SUPPLYQTY	DEMANDQTY	STSDESCR	MOVEIN	MOVEOUT	CANCELLED	PAB
2	01-31951-000	2,737	2,550	IV	MAIN		01/01/00	07/09/16	5/24/2017	-63	387	0	On Hand				387
3	01-31951-000	2,737	2,550	SOP	4003777		01/01/00	5/19/2017	5/24/2017	-63	0	150	Order				237
4	01-31951-000	2,737	2,550	SOP	4010189		01/01/00	5/24/2017	5/24/2017	-63	0	150	Order				87
5	01-31951-000	2,737	2,550	SOP	4010245		01/01/00	5/24/2017	5/24/2017	-63	0	150	Order				-63
6	01-31951-000	2,737	2,550	POP	PO193294		03/09/17	5/26/2017	5/24/2017	-63	850	0	Firm	X			787
Sheet20

Cell Formulas
Range	Formula
I2	{=INDEX(H$2:H$6,MATCH(1,($A2=$A$2:$A$6)*($Q$2:$Q$6<0),0))}
J2	{=INDEX(Q$2:Q$6,MATCH(1,($A2=$A$2:$A$6)*($Q$2:$Q$6<0),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jhammon2]

This worked great,

Thank you very much

Is this what you want?

Here I am assuming that you have more than one type of item in col A.

Unknown

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Textbox18	SUPPLYQTY1	DEMANDQTY1	MRPTYPE	ORDERNUMBER1	Item	RELEASEDATE_I	DUEDATE	Move in Date	Move in Qty	SUPPLYQTY	DEMANDQTY	STSDESCR	MOVEIN	MOVEOUT	CANCELLED	PAB
2	01-31951-000	2,737	2,550	IV	MAIN		01/01/00	07/09/16	5/24/2017	-63	387	0	On Hand				387
3	01-31951-000	2,737	2,550	SOP	4003777		01/01/00	5/19/2017	5/24/2017	-63	0	150	Order				237
4	01-31951-000	2,737	2,550	SOP	4010189		01/01/00	5/24/2017	5/24/2017	-63	0	150	Order				87
5	01-31951-000	2,737	2,550	SOP	4010245		01/01/00	5/24/2017	5/24/2017	-63	0	150	Order				-63
6	01-31951-000	2,737	2,550	POP	PO193294		03/09/17	5/26/2017	5/24/2017	-63	850	0	Firm	X			787

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet20​

Array Formulas Cell Formula I2 {=INDEX(H$2:H$6,MATCH(1,($A2=$A$2:$A$6)*($Q$2:$Q$6<0),0))} J2 {=INDEX(Q$2:Q$6,MATCH(1,($A2=$A$2:$A$6)*($Q$2:$Q$6<0),0))} <thead> </thead><tbody> </tbody> Enter the formula with "Ctrl+Shift+Enter". Only Pressing "Enter" will result in formula giving error or incorrect result. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>