Sorry for to mention copy the formula down from D2 as far as your records go.

Winnie

"winnie123" wrote:

Hi,

if you had an helper column in D of all the unique part numbers so using
your example

Col D
123
124
456
789

You can create this by entering =INDEX(B2:B7,MATCH(0,-ISBLANK(B2:B7),0)) in D1

Then In D2 enter this array formula using Crtl,shift & enter

=IF(COUNT(MATCH(B$2:B$7,D$1:D1,0))<COUNT(1/(B$2:B$7<"")),INDEX(B$2:B$7,MATCH(0,(B$2:B$7<"")-ISNA(MATCH(B$2:B$7,D$1:D1,0)),0)),"")


Then in Col E enter this array formula, so enter using ctrl,shift & enter to
get the curly brackets.

=SUM(($A$2:$A$7="Air")*($B$2:$B$7=D2)*($C$2:$C$7))
Copy down
This gives you the p/Number in D2 looks at the Air shipments then adds up
the qty

For Col F

Enter the same formula but just changed Air to ocean.

I hope this helps.


"tb" wrote:

Right now Col. E is blank, so I don't see the point in dragging it into the
pivot table like you suggest... I would like a formula that CREATES data
for cols. E, F, and G based on existing data in cols. A, B, and C.

On the other hand, if I were to create a pivot table by dragging, say, col.
B and then col. A to the row area (or vice versa first dragging col. A and
then col. B), and col. C to the column area, I still would not have
accomplished what I have in mind because the pivot table would present the
data summary in a vertical fashion whereas I would like to summarize data in
an horizontal fashion.

Let's assume that this is the raw data in cols. A-C:

Carrier Part No. Quantity
------- --------- ---------
AIR 123 10
AIR 124 20
AIR 123 50
OCEAN 456 60
OCEAN 789 50
OCEAN 123 20

What I would like to obtain in cols E-G is this:

Part No. AIR OCEAN
--------- ---- ---------
123 60 20
124 20 0
456 0 60
789 0 50

Thanks.
--
tb

"Ashish Mathur" wrote in message
...
Hi,

You may want to create a pivot table. Drag column E to the row area,
column B to the row area again and column C to the data area. Post back
and let us know how it worked

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"tb" wrote in message
...
I routinely download some sales order data from our ERP system into an
Excel spreadsheet:
* Column A has the shipping method of the goods (always either "AIR" or
"OCEAN").
* Column B has part numbers (there could be many instances of the same
part number in this column).
* Column C has the quantity shipped.

What I am after is a formula that will summarize such data:
* Column E would list each individual part number in alphanumerical
order. (No duplicate part numbers allowed!)
* Column F would have the grand total of all the quantities shipped by
AIR for each part number in Col. E..
* Column G would have the grand total of all the quantities shipped by
OCEAN for each part number in Col. E.

Data downloaded into Cols. A, B, C is quite large, so I would need a
fairly optimized formula for Cols. E, F, G. Also, it might be possible
to do what I want with a macro, but I really would prefer a standard
Excel formula due to my limited experience with macros and the fact that
the company I work for severely limits the usage of macros for fear of
viruses...

Thanks.
--
tb