Populate array with unique items and the total value of each

[Kendo]

I have a spreadsheet listing showing Part No, $ values and Region as per example below. I would like to populate an array with the unique part Nos and sum of their $ values for a nominated region. The array would then be used to enter the results into textboxes on a userform. For example for region b the result would be
1002 3,290.00
1005 -45.00

A B C
1 PartNo Amount Region
2 1002 100.00 a
3 1003 150.00 a
4 1002 145.00 b
5 1005 -45.00 b
6 1002 145.00 a
7 1002 3,000.00 b

Thanks for your help.

 

[gaz_chops]

Look at Pivot Tables.

[TABLE="width: 442"]
<tbody>[TR]
[TD]PartNo[/TD]
[TD]Amount[/TD]
[TD]Region[/TD]
[TD][/TD]
[TD]Sum of Amount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1002[/TD]
[TD="align: right"]100[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]Row Labels[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1003[/TD]
[TD="align: right"]150[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]395[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1002[/TD]
[TD="align: right"]145[/TD]
[TD]b[/TD]
[TD][/TD]
[TD]1002[/TD]
[TD="align: right"]245[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1005[/TD]
[TD="align: right"]-45[/TD]
[TD]b[/TD]
[TD][/TD]
[TD]1003[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1002[/TD]
[TD="align: right"]145[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]b[/TD]
[TD="align: right"]3100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1002[/TD]
[TD="align: right"]3,000.00[/TD]
[TD]b[/TD]
[TD][/TD]
[TD]1002[/TD]
[TD="align: right"]3145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1005[/TD]
[TD="align: right"]-45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]3495[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Beard]

I have a spreadsheet listing showing Part No, $ values and Region as per example below. I would like to populate an array with the unique part Nos and sum of their $ values for a nominated region. The array would then be used to enter the results into textboxes on a userform. For example for region b the result would be
1002 3,290.00
1005 -45.00

A B C
1 PartNo Amount Region
2 1002 100.00 a
3 1003 150.00 a
4 1002 145.00 b
5 1005 -45.00 b
6 1002 145.00 a
7 1002 3,000.00 b

Thanks for your help.

Sounds like a job for a Multidimensional Array.

Dim MyArray(2, 3) As String

for instance.

Note: this example won't create an array big enough for your needs, but you can change the values to suit your needs.

 

[Kendo]

I am able to achieve the result in stages by extracting all the data for the desired region and copying it to another sheet. then getting the unique part Nos and using that with the sumif function to get the total amounts. But I was wondering/hoping there was a better way?

 

[Kendo]

Hi gaz_chops,
I have never used pivot tables. How can I get the filtered data into an array.
Cheers