# Populate array with unique items and the total value of each

#### Kendo

##### New Member
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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Look at Pivot Tables.

Code:
``````[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]``````

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

Sounds like a job for a Multidimensional Array.

Code:
``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.

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?

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

Replies
10
Views
721
Replies
0
Views
72
Replies
7
Views
188
Replies
5
Views
290
Replies
1
Views
373

1,203,224
Messages
6,054,237
Members
444,711
Latest member
Stupid Idiot

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back