unique records and summing quantites

[Ramzes]

Hi All,

I have got a problem with finding a formula to sort and sum products.

Most of formulas I thoght of are only counting number of times it appears or comes back with the number of the unique records under one product.

I have two columns
In the first one I have product ID Numbers
In the second column I have Product quantities.

Ex.
bmok1298 50
cmsh4365 100

bmok1298 75
dngl3487 250

What I was trying to do is sum the quantities of the unique records and avoid the blanks.

So It should come back as:
bmok1298 125
cmsh4365 100
dngl3487 250

The worst thing is that I don't know what the product ID's will be as it will change every time.

Is there any thing that can be done to achieve that.

It would be 1 cell will be serching for the unique product ID's and listing them one under another, where the second cell will be summing all the figures referred to the record found by the first one.

Or shall I try with some kind of macro?

Thank you for reading it and cracking your head over it.

Simon

[NBVC]

Try using Pivot Tables:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

http://datapigtechnologies.com/ExcelMain.htm

[Ramzes]

Thank you for your help,

I have created a pivot table and it worked as I wanted. But here I have found new problem. As this will be a template file I need that table to automatically get the data from the Sheet1.Can this be done with Automatic Calculation on so every time I change data in the datasource it will automatically update itself with the product ID and qty?

Anyone who has any Idea please help.

I am still doing my own research on it as I have never done a pivot table before so it is new to me.

Thanks for reading it.

I hope I explained it well

[NBVC]

Check out here:

http://www.techonthenet.com/excel/macros/refresh_pt.php

[Ramzes]

Thanks Again NBVC,

I have done that code and it did automatically update itself.
Unfortunately only with the data that I was summing only. The product ID didn't go. The other problem twith it was - having your calculations on - workbook will never stop calculating. So can I assign a button to that VBA code like we do to macros? And make it update itself only by pressing a button.

I also need the first field to update itself (product ID's).

I'm trying and I'm trying and still in the same position.

I attach a file if it can help.

Please turn off your automatic calculation before opening the file (otherwise you will need to use the manager to close it).

Thanks for reading
Simon

[NBVC]

I am not sure what you mean...

I did not have calculation problems.. I see no formulas and your Pivot Table is okay...

You can hide the (blanks) row, by right-clicking there are choosing hide.

I added new Customer Part ID's and Shipped Quantities... and when I refresh the table they got added to the list....

To make the refresh on a button, remove the code you added for refreshing and instead add a new module to the editor window with code:

Please Login or Register  to view this content.

Then add a button to your sheet (go to View|Toolbars and select Forms to see the toolbar, select and drag the button icon onto your sheet, then assign the macro above to it....

[Ramzes]

Thank you for your help.

It runs perfectly.

Once again - thank you.
Simon