you're a true excel guru if..

[maks25]

Hi, I asked this question on another forum but no reply,

I am trying to create an array that would return unique sectors from column A, aggregate all the values (from column B) for each unique sector and then sort the new unique table from largest to smallest.

I have been able to accomplish everything but the sorting.

If you have any ideas on how to accomplish this please let me know.

Cell A13:

=INDEX($A$2:$A$8,MATCH(0,IF(ISBLANK($A$2:$A$8),"",COUNTIF($A$12:A12,$A$2:$A$8)),0))

Cell B13:

=SUMIFS($B$2:$B$8,$A$2:$A$8,A13)

hope it's not against the rules..but here is the xlsx

http://www.woofiles.com/dl-261987-VMPBFpbw-excelforumquestion.xlsx

thanks


edit:
I do have a limitation. I cannot just link to cells A12:B17 with another custom formula to sort it because of the layout on the sheet, otherwise I would have done just that.

 

[dave3009]

You could use a Pivot Table

Excel Workbook
	A	B
1	A	B
2	Utilities	1.30%
3	Resource	1.60%
4	Consumer	1.55%
5	REITs	2.05%
6	Industrial	1.30%
7	REITs	1.60%
8	REITs	1.60%
9
10
11
12	Row Labels	Sum of B
13	REITs	5.25%
14	Resource	1.60%
15	Consumer	1.55%
16	Utilities	1.30%
17	Industrial	1.30%
18	Grand Total	11.00%
Sheet3

Excel 2007

 

[maks25]

Thanks for your reply!

I have thought of that, however unfortunately i am working with live data..the idea is that this table would update itself without running Macros or any other user input.

I feel like I will have to develop a UDF...the one area I don't have much expertise in just yet hah

 

[dave3009]

Live data, how is the data imported?

If you're thinking about a UDF then you'd be as well considering a macro that refreshes your Pivot Table when the live data is updated or in a particular time frame.

 

[maks25]

It's stock quotes and other financial data etc. Which gets updated every tick (a few times a second). (RTD)

UDF seems to be the only way to go

 

[Marcelo Branco]

Maybe this Array-formula in A13 (Excel 2007 or higher)

=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($A$2:$A$8,"<"&$A$2:$A$8)-SUM(COUNTIF($A$12:A12,$A$2:$A$8)),0)),"")

Ctrl+Shift+Enter

copy down

HTH

M.

 

[Marcelo Branco]

Forgot...

B13
=SUMIF($A$2:$A$8,A13,$B$2:$B$8)

copy down

M.

 

[maks25]

Thanks!

However it doesn't seem to work, I get:

<table width="165" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3584;width:76pt" width="101"> <tbody><tr style="height:14.4pt" height="19"> <td colspan="2" class="xl66" style="border-right:1.0pt solid black; height:14.4pt;width:124pt" height="19" width="165">Sector Exposure</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl68" style="height:14.4pt" height="19">Consumer</td> <td class="xl69">1.55%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl68" style="height:14.4pt" height="19">Industrial</td> <td class="xl69">1.30%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl68" style="height:14.4pt" height="19">REITs</td> <td class="xl69">5.25%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl68" style="height:14.4pt" height="19">Resource</td> <td class="xl69">1.60%</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl68" style="height:14.4pt" height="19">Utilities</td> <td class="xl69">1.30%</td> </tr> </tbody></table>

 

[Marcelo Branco]

Thanks!

However it doesn't seem to work, I get:

Is it not correct?

M.
ps: i have to say: this formula was created by Richard Schollar

 

[maks25]

Is it not correct?

M.
ps: i have to say: this formula was created by Richard Schollar

Unfortunately not, for it to be correct it would have to show:

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:14.4pt" height="19"> <td colspan="2" class="xl66" style="border-right:1.0pt solid black; height:14.4pt;width:96pt" height="19" width="128">Sector Exposure</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">REITs</td> <td align="right">5.25</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Resource</td> <td align="right">1.6</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Consumer</td> <td align="right">1.55</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Utilities</td> <td align="right">1.3</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Industrial</td> <td align="right">1.3</td> </tr> </tbody></table>


And yea, probably. I wrote it a few months ago and googled my *** off lol.