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

[SkjaldOfRuss]

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.

 

[SkjaldOfRuss]

It appears you cannot edit. My apologies. Better formatting here:

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

Model	Rank	Security	True	Gross
1	1	????????? - To be solved.	Formula at bottom of above comment.	Same formula but Strategy True changed to Strategy Gross.
1	2
1	3
1	4
1	5
1	6
1	7
1	8
1	9