consolidate?

[offroadcyclist]

here's what I'm trying to do:

I have 1 column with a unique item description in the form of a par
number and a column next to it with a quantity. The descriptions ar
repeated with different quantities. I would like to have the tota
quantity for each part number on 1 row.

something like this:

part # qty
53359 5
53359 2
53360 4
53360 -2

result:

part # qty
53359 7
53360 2


thanks in advance for you help

 

[Bernard Liengme]

Read in Help about SUMIF and Pivot Tables; either of these will solve your
problem
Then come back if you need more help

best wishes

Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

 

[Guest]

There are several ways. One is to click on any cell in the group and use
Data/PivotTable and PivotChart Report, then click finish; Then drag the Part
# to the Row field, and the qty to the Data Field, and you're done.
Another way is to use a formula - SUMIF, but you need to set up the unique
part#s. Select the first column (part#s), use Data/Filter/Advanced Filter,
click Unique Records only, click Copy to another location, click in the Copy
To box, then click a cell in the worksheet for the list of unique part#s, say
D1, then click OK. In E2 (D1 has the heading), enter
=SUMIF($A$2:$A$30,D2,$B$2:$B$30) and fill down -- "30" was assumed to be the
last row used.
There are other ways as well, but this should get you started.
HTH
Bob Umlas
Excel MVP

 

[VJ7777]

-----Original Message-----
here's what I'm trying to do:

I have 1 column with a unique item description in the form of a part
number and a column next to it with a quantity. The descriptions are
repeated with different quantities. I would like to have the total
quantity for each part number on 1 row.

something like this:

part # qty
53359 5
53359 2
53360 4
53360 -2

result:

part # qty
53359 7
53360 2


thanks in advance for you help!

I'm sure others will point you to simple existing
capabilities in Excel such as pivot tables or some such
which will give you the answer you are searching for.
I've never used those capabilities because I always want
answers in some oddball format. One formula has solved
almost all of my reporting problems in Excel. If I
understand your request, you want a report that
summarizes the quantity of each part number. Let's make
it more comples and say you have three columns and 400
lines of data:

Column A Column B Column C
1 Parts List:
2
3 Part Number SubAssembly Number Quantity
4 12345 xyz1 2
5 15698 abc2 4
....
400 12345 tuv29


Report 8.29.04:

Part SubAssembly On Hand Sold Returned On Hand
8.22.04 8.29.04

12345 xyz1 100 10 2 92
12345 tuv29 150
15698 xyz1