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

##### New Member
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>

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### RonB1111

##### Well-known Member
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.

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

#### RonB1111

##### Well-known Member
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.

Replies
3
Views
106
Replies
3
Views
124
Replies
7
Views
364
Replies
13
Views
398
Replies
3
Views
133

1,195,723
Messages
6,011,315
Members
441,604
Latest member
CraigThompson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back