Sorting & Combining Rows

L

LaNaye

I am trying to figure out how I can sort these rows and then combine the like
rows. Obviously, I am not describing it well, but this is what I want:

Start with:

Column (A) Qty per system; (B) # of Systems; (C) Total Qty (D) Part #; (E)
Part Description

1 2 2 W0000 Widgets
1 3 3 G0000 Gidgets
1 4 4 D0000 Didgets
1 2 2 W0000 Widgets
1 2 2 W0000 Widgets
1 3 3 G0000 Gidgets
1 4 4 D0000 Didgets
1 2 2 W0000 Widgets

I can sort and end up with:

1 2 2 W0000 Widgets
1 2 2 W0000 Widgets
1 2 2 W0000 Widgets
1 2 2 W0000 Widgets
1 3 3 G0000 Gidgets
1 3 3 G0000 Gidgets
1 4 4 D0000 Didgets
1 4 4 D0000 Didgets


What I want to do is combine the like part numbers; add the qtys and end up
with this:
A B C D E
1 2 8 W0000 Widgets
1 3 6 G0000 Gidgets
1 4 8 D0000 Didgets

The only number I am really concerned about combining is the total qty. (C)
The rest is irrelevant. Is this do-able? If so, I would sure appreciate a
detailed method of getting this done. Please keep in mind that I am
relatively new to Excel. Thank you in advance.
 
D

Dave Peterson

After you sort the data, try Data|subtotals (twice).

Or you may want to learn a bit about data|Pivottable.
 
S

Sheila D

Hi there
Have a look at the Subtotals command on the Data menu - should do what you
want I think. First sort the table on Part, then choose the command.
1st box should say 'at exch change in' PART
Then use the Count function in the 2nd box and in the last box tick the
fields you want values for. Hope it works

Sheila
 
L

LaNaye

Sheila,

Thank you very much - it works! I did have to use SUM function instead of
COUNT because I wanted the total number of parts. Thank you for your help.

LaNaye
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top