Need Formula For Data Summary

[tb]

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.

 

[Ashish Mathur]

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]

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.

 

[winnie123]

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$11,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$11,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.

 

[winnie123]

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

Winnie

 

[Ashish Mathur]

Hi,

To get the data in a horizontal fashion, drag the Data heading in the pivot
table and drop it on the Total heading in the pivot table

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[tb]

Thanks, Ashish, generally speaking your suggestion would certainly work!
I would nevertheless prefer somebody to come up with formulas in cols. E-G
because I am planning on linking data in those columns with another workbook
and I don't think that a static pivot table would do. Quantity and content
of my raw data changes every time I download it and therefore I would have
to continuously redo the pivot table and the linking to the other
workbook...

 

[tb]

Thanks for your help! I quickly tested your formulas and they seem to be
working. I will do a more comprehensive test on Monday and we will see..

Best regards.