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

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

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

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

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

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" wrote in message
...
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

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

"Ashish Mathur" wrote in message
...
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" wrote in message
...
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

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.
--
tb

"winnie123" wrote in message
...
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