Combining equal rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ceab - 26 Sep 2007 12:26 GMT

I have a fairly large file with about 10000 rows.
I need to comine the rows with equal product numbers and add the amounts of
these so that any product number only occurs once.
The product description can be left out if it makes it easier.
Are there any programs that can do this, or a script?

What I have now:
Product nr. Product descr. Amount

12345 Gasket 45 15
12345 Gasket 45 20
12111 Bearring 1 5
12333 Bearring 2 3
12311 Bearring 1 4

The result I want:
Product nr. Product descr. Amount

12345 Gasket 45 35
12111 Bearring 1 9
12333 Bearring 2 3

Any help much appreciated!

Reply to this Message

OssieMac - 26 Sep 2007 13:02 GMT

I suggest that you look into using a pivot table. Some people find them a
little overwhelming at the start but they are certainly worth the effort to
master them.

Regards,

OssieMac

> I have a fairly large file with about 10000 rows.
> I need to comine the rows with equal product numbers and add the amounts of
[quoted text clipped - 19 lines]
>
> Any help much appreciated!

Reply to this Message

Dave Peterson - 26 Sep 2007 13:10 GMT

You could sort your data by number, then description, then use data|subtotal and
subtotal (twice) by the description and number. Then use the outlining symbols
to hide the details.

Or you could use data|pivottable to get a nice summary report of your data.

Select your range A1:Cxxx
Data|pivottable
follow the wizard until you get to a step that has a button named Layout.
Click that Layout button

Drag the number to the row field
drag the description to the row field
drag the amount to the data field (it should say Sum of)

And finish up.

> I have a fairly large file with about 10000 rows.
> I need to comine the rows with equal product numbers and add the amounts of
[quoted text clipped - 19 lines]
>
> Any help much appreciated!

Signature

Dave Peterson

Reply to this Message

Rate this thread: