Sorting & Combining Rows
 

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.

Sorting & Combining Rows
 

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

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

LaNaye wrote:

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.

--

Dave Peterson

Sorting & Combining Rows
 

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

"LaNaye" wrote:

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.

Sorting & Combining Rows
 

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


"Sheila D" wrote:

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

"LaNaye" wrote:

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.