Extracting the name and % value of the top 20 data entries - Help...

SkjaldOfRuss

New Member
Joined
Mar 15, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Please forgive any formatting issues. First time posting to MrExcel so if it looks horrendous please give me a few minutes to fix it.

I've been stumped on this for a few days and I'm going mad. Really need help. I'm above average with excel but this is driving me mad. I need to pull the top 20 investor holdings from multiple data sources and different strategies.

Excel: 2016

Workbook:

7 individual sheets which are pulled from a separate source. This contains all the underlying data for the 7 specific strategies they relate to and are named 1, 2, 3 etc. It's just raw data, no impact to the formulas.

A merged sheet which pulls all this data into 1 sheet as there is overlap between the strategies and they need to be summed together. Again, it's still just the raw data so no direct impact.

Pivot tables pulled from the merged sheet data in the below format named Strategy True and Strategy Gross. Letters represent the actual security and the numbers represent the strategy.

|Name|1|2|3|4|5|6|7|
:--|:--|:--|:--|:--|:--|:--|:--|
|A|%|%|%|%|%|%|%|
|B|%|%|%|%|%|%|%|
|C|%|%|%|%|%|%|%|

A summary sheet that has tables in the following format:

|Model|Rank|Security|True|Gross||
:--|:--|:--|:--|:--|:--|
|1|1|?????????|See below for formula|Same formula but "Strategy Gross"||
|1|2|?????????||||
|1|3|?????????||||
|1|4|?????????||||
|1|5|?????????||||
|1|6|||||
|1|7|||||
|1|8|||||
|1|9|||||
|1|10|||||
|1|11|||||
|1|12|||||
|1|13|||||
|1|14|||||
|1|15|||||
|1|16|||||
|1|17|||||
|1|18|||||
|1|19|||||
|1|20|||||
||Total||Sum|Sum||

I can pull the % value easily enough from the pivot tables using the below formula:

{=Large(If('Strategy True'!$A:$A<>"Grand Total",'Strategy True'!$B:$B),B2)}

This works to ignore the Grand Total value at the bottom and return the largest values in position 1, 2 etc based on B2/B3 etc. % is fine. I've manually verified this. For strategy 2, I just change it to Column C. Strategy 3 - Column D. Now there ARE duplicates of %'s which is causing the issue.

How can I return the security name based on the Top 20 largest values?

Like I say, I'm above average but I'm really struggling here.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

SkjaldOfRuss

New Member
Joined
Mar 15, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
It appears you cannot edit. My apologies. Better formatting here:

Name1234567
A
%
%
%
%
%
%
%
B%
%
%
%
%
%
%
C%
%
%
%
%
%
%

ModelRankSecurityTrueGross
11????????? - To be solved.Formula at bottom of above comment.Same formula but Strategy True changed to Strategy Gross.
12
13
14
15
16
17
18
19
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

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