I have a report grouped by part number, due date and order number to identify our manufacturing shortages and supply coming in. Our ERP system does not hard allocate therefore we are trying to do this in a CR. I was able to create a running total for every group of part numbers. Im trying to create a formula to identify a negative value in my running total and display its next purchase supply order record and due date to allign in the same row as the part number with the negative running total. Trick is its by part number, therefore I dont want the formula to pull another part numbers supply data. I appologise if I'm not using the correct terminology. But how I would describe in excel, I would use a index/match formula to find the next purchase order. Our shortage report when exported is 32,000 lines long.

 

If you scroll down I pasted in an example, but there is a huge gap of spacing to the bottom of the page.

 

Anyones help would be greatly appreciated.

 

Column Letters:	D	J	P	K	L	M	V	W
Column	PrtNum	OrderNUm	DueDate	DueQty	OnHand	RunTTL	SupplyOrder#	SupplyOrderDate
Shortage	123456	500	1/25/2010	-1	2	1	Ok	Ok
Shortage	123456	501	1/26/2010	-3		-2	700	1/28/2010
Shortage	123456	502	1/27/2010	-2		-4	700	1/28/2010
Supply	123456	700	1/28/2010	5		1	No Data Since it’s a Supply Row
Shortage	123456	503	1/30/2010	-3		-2	N/A	N/A
Shortage	999999	505	1/25/2010	-10	1	-9	715	1/28/2010
Shortage	999999	506	1/26/2010	-3		-12	715	1/28/2010
Shortage	999999	507	1/27/2010	-10		-22	716	1/28/2010
Supply	999999	715	1/28/2010	20		-2	716	1/28/2010
Shortage	999999	509	1/30/2010	-3		-5	No Data Since it’s a Supply Row
Shortage	999999	515	2/5/2010	-20		-25	716	1/28/2010
Supply	999999	716	2/5/2010	30		5	No Data Since it’s a Supply Row