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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,493
Messages
5,837,673
Members
430,509
Latest member
steve85215

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