sales order worksheet- identify same number in one column and subtract demand from supply

[BadHabit]

I currently use Excel 2007 at work, but may upgrade to 2010 soon. I have a large sales report dump into Excel that does not allocate supply to demand. I only want to look at the sales orders that I'm short on inventory. If I have 10 separate sales (rows) of the same item and have 5 in stock; all 10 rows show a qty of 5 in stock. I currently insert an extra column and use a formula (=if(A2<>A1,"1st","duplicate") to identify the "1st" of each new part number in the column and then filter the data by 1st & duplicate and use a different SUM formula for each one. When I remove the filter it calculates properly.

I would like to be able to do this in one column with one formula and not have to use 3 separate formulas and filters to make it work. I want to set up a template that I can just dump my report into. One constant in the report is that it is in order by part number then by date. Below is a basic example of the report. Sorry for the crude worksheet, but I this is my 1st post and I couldn't seem to paste in one from Excel. The "Balance" column is one that I add in each time to create my allocation formulas and would love to have a better way of doing it! Thanks for your help!

PART #						S.O. #																																	DATE																																QTY	ON HAND																					BALANCE
603						101123																																	26-Mar																																1	2																					1
603						101124																																	27-Mar																																1	2																					0
603						101125																																	28-Mar																																2	2																					-2
603						101126																																	29-Mar																																1	2																					-3
603						101127																																	30-Mar																																1	2																					-4
603						101128																																	31-Mar																																2	2																					-6
603						101129																																	1-Apr																																1	2																					-7
603						101130																																	2-Apr																																10	2																					-17
604						101749																																	12-Apr																																2	0																					-2
604						101777																																	13-Apr																																4	0																					-6
604						101982																																	14-Apr																																2	0																					-8
604						102110																																	15-Apr																																5	0																					-13
10800						999987																																	5-Apr																																1	5																					4
10800						999982																																	6-Apr																																1	5																					3
10800						100092																																	7-Apr																																1	5																					2
51444-SERVICE						112312																																	28-Mar																																25	100																					75
51444-SERVICE						112331																																	29-Mar																																5	100																					70
51444-SERVICE						112345																																	30-Mar																																15	100																					55
51444-SERVICE						112719																																	31-Mar																																12	100																					43
019050 225						101123																																	12-Apr																																2	10																					8
019050 225						101982																																	13-Apr																																5	10																					3
019050 225						101777																																	14-Apr																																3	10																					0
019050 225						101130																																	15-Apr																																1	10																					-1

<tbody>
</tbody>

 

[RonB1111]

Assume:
1. headers are in row 1
2. data begins in rows 2
3. Date is col A
4. Qty is col BT (when I copied your data, this is the column it appears in)
5. OnHand is col BU

This formula will give you the "Balance" you showed in the last col. No helper columns or filtering is needed. Enter it in row 2 of your balance column and copy down:
=BU2-SUMIF(A$2:A2,A2,BT$2:BT2)

If your Qty and OnHand are actually in different columns, you'll need to adjust the formula accordingly.

 

[BadHabit]

Thanks, Ron! That works perfectly! Now I just want to understand why it works...LOL.

 

[RonB1111]

You're welcome. The reason it works is because your data is in part # order and the Sumif does not look below the current row - eg: if copied down to row 5, it looks at only rows 2 thru 5 to sum the qty used to that point. Hope that helps in understanding what the formula is doing.