Populate array with unique items and the total value of each

Kendo

New Member
Joined
Jul 11, 2012
Messages
20
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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]
 

Beard

New Member
Joined
May 24, 2015
Messages
14
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.

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.
 

Kendo

New Member
Joined
Jul 11, 2012
Messages
20
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

New Member
Joined
Jul 11, 2012
Messages
20
Hi gaz_chops,
I have never used pivot tables. How can I get the filtered data into an array.
Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top