Hello,

I have a 2 dimensional array with 3 colums, consisting of a product column,
a number column and a column for the amount of the product used.
Like this:

Calcium 123 50(g)
Potassium 245 10
Zinc 360 15
Calcium 123 25

What I want to do is loop through the array and make a new array, but with
all the Calcium with the same number (123) only mentioned ONCE and with the
amounts used added, like this.

Calcium 123 75
Potassium 245 10
Zinc 360 15

So I want to put in the new array:
* all unique products and
* the added amounts of all the products that appear more than once and have
the same number.

I'm quite a VBA newbie and already tried several times to find a solution,
but without any luck.
Any help would be great.

Thanks in advance,

David

This would be tailor made for a pivot table.

Just make the first two columns as row field and the last column as you data
field. You don't need a Column field. I assume the (g) is not actually in
the cell. If so, you would need to remove this to make them all numbers.

Just select all your data and do Data=Pivot Table Report and follow the
wizard. If you are in Excel 2000 or later, you need to hit the layout
button on the last dialog of the wizard to specify your field locations.
Also, you data would need a heading in the first row like

Chemical Type Amount

An alternative is to set it up this way, then sort the data on columns 1 and
2, then use Data=Subtotal and select Amount as the column to subtotal and
type as the controlling column . This will put subtotals below each
grouping. You can collapse the outline on the left to only show the
subtotals. You will need to fill in the first column to see the
hemical. - probably easier to use a pivot table.

--
Regards,
Tom Ogilvy

"David Van Heuverswyn" wrote in message
...
Hello,

I have a 2 dimensional array with 3 colums, consisting of a product
column,
a number column and a column for the amount of the product used.
Like this:

Calcium 123 50(g)
Potassium 245 10
Zinc 360 15
Calcium 123 25

What I want to do is loop through the array and make a new array, but with
all the Calcium with the same number (123) only mentioned ONCE and with
the
amounts used added, like this.

Calcium 123 75
Potassium 245 10
Zinc 360 15

So I want to put in the new array:
* all unique products and
* the added amounts of all the products that appear more than once and
have
the same number.

I'm quite a VBA newbie and already tried several times to find a solution,
but without any luck.
Any help would be great.

Thanks in advance,

David

Hi Tom,

Thanks for the respons. I really appreciate the help.
And it works fine too.
I just have one more question. Is there a way to let the end of the cell
range change dynamically ?

I'll try to explain what I mean:
I have made a userform so people can easily type in the chemical they
used, with the corresponding number and the amount they used.
These values are written to a table in a worksheet. So the end of the
table changes dynamically. Chemicals are added to the end of the table.
Then I made a userform that lets the people give in the year and quarter
for which they want to know the total amount of products used. (actually
the total stockvalue, but that's not important)
So this is where the array is created: the products with their
particular number and the amounts used (for a certain quarter of a
certain year) are saved in the array I posted in the first message.
This is all done through VBA. When the user clicks a button after giving
the year and quarter, I want him to see the total amount used and I want
this to be written to a spreadsheet.
Can I create a Pivottable with VBA ? So when the user clicks the button,
the Pivottable is automatically created.

Thanks a lot,

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Just turn on the macro recorder while you create the pivot table manually.
This will give you the code you need.

You can then modify the code to make the range for the pivot table dynamic -
although it might be easier to use a named range

insert - name - define

Name: Database
Refersto: =offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),3)

Then use this defined name as the source for you pivot Table.

--
Regards,
Tom Ogilvy

"David Black" wrote in message
...
Hi Tom,

Thanks for the respons. I really appreciate the help.
And it works fine too.
I just have one more question. Is there a way to let the end of the cell
range change dynamically ?

I'll try to explain what I mean:
I have made a userform so people can easily type in the chemical they
used, with the corresponding number and the amount they used.
These values are written to a table in a worksheet. So the end of the
table changes dynamically. Chemicals are added to the end of the table.
Then I made a userform that lets the people give in the year and quarter
for which they want to know the total amount of products used. (actually
the total stockvalue, but that's not important)
So this is where the array is created: the products with their
particular number and the amounts used (for a certain quarter of a
certain year) are saved in the array I posted in the first message.
This is all done through VBA. When the user clicks a button after giving
the year and quarter, I want him to see the total amount used and I want
this to be written to a spreadsheet.
Can I create a Pivottable with VBA ? So when the user clicks the button,
the Pivottable is automatically created.

Thanks a lot,

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!