I have a long list of items with amounts sold and unit prices. I want to add
up anything that is the same item and price.

Example:
Qty Item: Price
1 TC001 $23
15 TC001 $20
3 TC001 $23
2 TC002 $44
2 TC003 $231
2 TC001 $23
1 TC003 $231
6 TC002 $40

Should return something like

Qty Item Price Total
6 TC001 $23 $138
15 TC001 $20 $300
2 TC002 $44 $88
6 TC002 $40 $240
3 TC003 $231 $693

I will have many different items and different prices.

TIA!

Rich

I would use a pivot table. Info available he
http://www.cpearson.com/excel/pivots.htm

Dave
--
Brevity is the soul of wit.


"Rich" wrote:

I have a long list of items with amounts sold and unit prices. I want to add
up anything that is the same item and price.

Example:
Qty Item: Price
1 TC001 $23
15 TC001 $20
3 TC001 $23
2 TC002 $44
2 TC003 $231
2 TC001 $23
1 TC003 $231
6 TC002 $40

Should return something like

Qty Item Price Total
6 TC001 $23 $138
15 TC001 $20 $300
2 TC002 $44 $88
6 TC002 $40 $240
3 TC003 $231 $693

I will have many different items and different prices.

TIA!

Rich

Hmmm.... Looks confusing. I'll give it a go.

So it will output a table for me? Like this?

Qty Item Price Total
6 TC001 $23 $138
15 TC001 $20 $300
2 TC002 $44 $88
6 TC002 $40 $240
3 TC003 $231 $693


"Dave F" wrote:

I would use a pivot table. Info available he
http://www.cpearson.com/excel/pivots.htm

Dave
--
Brevity is the soul of wit.


"Rich" wrote:

I have a long list of items with amounts sold and unit prices. I want to add
up anything that is the same item and price.

Example:
Qty Item: Price
1 TC001 $23
15 TC001 $20
3 TC001 $23
2 TC002 $44
2 TC003 $231
2 TC001 $23
1 TC003 $231
6 TC002 $40

Should return something like

Qty Item Price Total
6 TC001 $23 $138
15 TC001 $20 $300
2 TC002 $44 $88
6 TC002 $40 $240
3 TC003 $231 $693

I will have many different items and different prices.

TIA!

Rich

Yes pivot tables can be formatted in that manner. You'll find they're a
fairly powerful tool for summarizing a lot of data quickly.

Dave
--
Brevity is the soul of wit.


"Rich" wrote:

Hmmm.... Looks confusing. I'll give it a go.

So it will output a table for me? Like this?

Qty Item Price Total
6 TC001 $23 $138
15 TC001 $20 $300
2 TC002 $44 $88
6 TC002 $40 $240
3 TC003 $231 $693


"Dave F" wrote:

I would use a pivot table. Info available he
http://www.cpearson.com/excel/pivots.htm

Dave
--
Brevity is the soul of wit.


"Rich" wrote:

I have a long list of items with amounts sold and unit prices. I want to add
up anything that is the same item and price.

Example:
Qty Item: Price
1 TC001 $23
15 TC001 $20
3 TC001 $23
2 TC002 $44
2 TC003 $231
2 TC001 $23
1 TC003 $231
6 TC002 $40

Should return something like

Qty Item Price Total
6 TC001 $23 $138
15 TC001 $20 $300
2 TC002 $44 $88
6 TC002 $40 $240
3 TC003 $231 $693

I will have many different items and different prices.

TIA!

Rich

Hi Rich

In addition to what DaveF has said, you would need to add a calculated
field to the PT to generate exactly what you want.
Mark your block of dataDataPivot TablesNextLayout
Drag Item to the Row area, drag Qty to the Row area below Item
Drag Price to the Data area, then double click on the field and choose
Sumclick OKFinish
On the new tab with the PT, from the PT toolbar, use the dropdown and
select FormulasCalculated field
In the Name box Type Value and in the Formula box type = Qty * PriceOK
Now on the PY, drag the Data button across to the word Total, and you
will have Price and Value side by side.

If there are subtotals appearing and you don't want them, then double
click the field heading and choose SubtotalsNone

For more information on how to set up and use PT's take a look at the
following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx

--
Regards

Roger Govier


"Rich" wrote in message
...
Hmmm.... Looks confusing. I'll give it a go.

So it will output a table for me? Like this?

Qty Item Price Total
6 TC001 $23 $138
15 TC001 $20 $300
2 TC002 $44 $88
6 TC002 $40 $240
3 TC003 $231 $693


"Dave F" wrote:

I would use a pivot table. Info available he
http://www.cpearson.com/excel/pivots.htm

Dave
--
Brevity is the soul of wit.


"Rich" wrote:

I have a long list of items with amounts sold and unit prices. I
want to add
up anything that is the same item and price.

Example:
Qty Item: Price
1 TC001 $23
15 TC001 $20
3 TC001 $23
2 TC002 $44
2 TC003 $231
2 TC001 $23
1 TC003 $231
6 TC002 $40

Should return something like

Qty Item Price Total
6 TC001 $23 $138
15 TC001 $20 $300
2 TC002 $44 $88
6 TC002 $40 $240
3 TC003 $231 $693

I will have many different items and different prices.

TIA!

Rich