Combine and delete duplicate rows...dynamically

I need some help with this! I have a parts list that can be very long. If the same part shows up in more than one row I want to add the totals and remove the duplicate part number's row. Below is an example of what I want to accomplish...

I have this...

P/N.......... Description............... U/M..........Qty
1234.........rivet..........................ea.............1
1234.........rivet..........................ea.............4
345...........washer.....................ea.............12
65.............nut...........................ea.............4
3846.........prepreg.....................sf..............24
3846.........prepreg.....................sf..............12
64.............primer......................ga.............14

I need it to look like...

P/N..........Description................ U/M..........Qty
1234.........rivet..........................ea.............5
345...........washer.....................ea.............12
65.............nut...........................ea.............4
3846.........prepreg.....................sf.............36
64.............primer......................ga.............14

As you can see the part numbers (p/n) 1234 and 3846 now show totals and are not duplicated. This also needs to happen dynamically because this parts list changes all the time. I would like not to have to do a macro for this but if it's the only way then I will. Please help! Thanks!

Richard,
Your direction lead me down the right path and I ended up getting it to work dynamically. Thanks again.

Stanley,
I'll try that macro to just to see how it works. Thanks

Stanley,

Thank you! Your Macro was exactly what I have been looking for.

Tammy

Stan,

Ok, I've hit a snag. The report I want to apply the macro to is separated by Market (row with just the market designator in column A). If I take out the separation and run the macro, it works great. If I run the report as intended, it "breaks".

I'm attempting to attach two files: Ops per Location is the format I am suppose to provide and Ops per Location Simple is the one I ran the macro on and worked.

Any suggestions on what I am doing wrong or what I can do differently?

Thank you SO much!

Tammy
Ops per Location Simple2.xls
Ops per Location.xls

I am SO sorry! I have never used forums before and wasn't aware of the etiquette - I'll start a new thread. My appologies for the inadvertent hijacking.

Tammy

Can't download the example.