merge identical fields and generate totals.

**apeman1977** · 03-18-2009, 09:53 AM

I don't know whether anyone can help or not but i have a stock usage report i am doing and i would like to know if i can combine or merge into one line identical part numbers and recalculate the totals.

Cheers in advance Dave

**NBVC** · 03-18-2009, 09:55 AM

A sample worksheet showing your current setup and your desired results would help.

**apeman1977** · 03-18-2009, 10:03 AM

On sheet 2 of the worksheet there are part numbers in column A. of these there are multiple entry's. i am hoping to create just one entry but have a recalculated total against that one.

eg. row 14 -19 are the same part numbers with a total of 496. i would like to make this just a single row with a total of 496. and of course update the costs as well.

cheers Dave

**NBVC** · 03-18-2009, 10:08 AM

What about the costs? Which one to take?

Maybe, instead you can add a formula in the next column that shows the total quantity for the part... e.g... stick this formula in E2: =SUMIF(A:A,A2,C:C)

and copy it down the entire list... now you have the total quantity for the part in that particular row.

Does that work for you?

**apeman1977** · 03-18-2009, 10:19 AM

Not quite what i was chasing.

The boss is probably still going to ask for just a single line if possible.

is there a way of making it a single line at all? if there is that would be desirable. The costs from the same part number would also need to be added up like the quantities as well.

If there is no way of doing this i will just have to let him know and go from there, or pull the individual types of part number and add them up and then put them into a new sheet.

Dave

**NBVC** · 03-18-2009, 10:57 AM

You could do it with a few steps...

First, put formula in E2: =SUMIF($A:$A,$A2,C:C) and copy to F2, then copy down the list...

Then copy columns E and F, select F1 and go to Edit|Paste Special and select Values... Click Ok.

Then delete old columns C and D.

Then click on A2 and go to Data|Filter|Advanced Filter.

Select Filter List in Place

List Range: select the entire range to filter.

Select Unique Records Only.

Click Ok...

should have new unique list...

See attached for what it should look like.

**apeman1977** · 03-18-2009, 11:23 AM

NBVC, you are an absolute legend and if i could hand you a beer right now i would do it.

That is just want i wanted (hopefully it pleases the boss too).

Thank you again, you information has been invaluable.

Regards Dave

**NBVC** · 03-18-2009, 11:30 AM

Great! You are welcome.. I could probably have used that beer about now..

But I will just pretend I got it from you anyways.

Can you please mark the thread as solved:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save