Question about extracting data from a pivot - top 5 results

MarkTheRed

New Member
Joined
Jan 5, 2017
Messages
7
Hello everyone,

First time posting here, i am really hoping someone could help me with this, because i have no idea how to proceed further here.

I am trying to extract top 5 numbers from 2 categories in a pivot, i have named them grade 1 and grade 2 (please see screenshot attached, since i am unable to attach anything to the post). I am able to extract the top values using =large (if someone has a better idea i am open to suggestions).

My issue is, i simply cannot find a way to match the values to the names that go with them. The sort pivot options won't work because of the two categories.

Please pay in mind that i wish to avoid using any macros at all, so i am trying to find a solution via a simple formula. In the worst case, i can simply create 2 pivots but that's really not my goal here if i can avoid it.

Any help would be really appreciated in my learning curve in excel :).

Thank you all in advance.



screen.png
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Hi,

take a look at this:

<b>Excel 2016 (Windows) 64 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">name</td><td style=";">Type</td><td style=";">Grade 1</td><td style=";">Grade 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Ana</td><td style="text-align: right;;">1</td><td style="text-align: right;;">72</td><td style="text-align: right;;">51</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Mark</td><td style="text-align: right;;">1</td><td style="text-align: right;;">34</td><td style="text-align: right;;">66</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Nicole</td><td style="text-align: right;;">2</td><td style="text-align: right;;">85</td><td style="text-align: right;;">34</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">John</td><td style="text-align: right;;">1</td><td style="text-align: right;;">93</td><td style="text-align: right;;">91</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Marcus</td><td style="text-align: right;;">2</td><td style="text-align: right;;">85</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Stephen</td><td style="text-align: right;;">1</td><td style="text-align: right;;">85</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">James</td><td style="text-align: right;;">1</td><td style="text-align: right;;">41</td><td style="text-align: right;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Jack</td><td style="text-align: right;;">2</td><td style="text-align: right;;">61</td><td style="text-align: right;;">49</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Phil</td><td style="text-align: right;;">2</td><td style="text-align: right;;">69</td><td style="text-align: right;;">82</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Sam</td><td style="text-align: right;;">1</td><td style="text-align: right;;">41</td><td style="text-align: right;;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Jackie</td><td style="text-align: right;;">2</td><td style="text-align: right;;">62</td><td style="text-align: right;;">86</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Melisa</td><td style="text-align: right;;">1</td><td style="text-align: right;;">43</td><td style="text-align: right;;">74</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Johnson</td><td style="text-align: right;;">2</td><td style="text-align: right;;">88</td><td style="text-align: right;;">36</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style=";">Top 5</td><td style=";">Grade 2 T1</td><td style="text-align: right;;"></td><td style=";">Top 5</td><td style=";">Grade 2 T2</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style=";">John</td><td style="text-align: right;;">91</td><td style="text-align: right;;"></td><td style=";">Jackie</td><td style="text-align: right;;">86</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style=";">Melisa</td><td style="text-align: right;;">74</td><td style="text-align: right;;"></td><td style=";">Phil</td><td style="text-align: right;;">82</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;"></td><td style=";">Mark</td><td style="text-align: right;;">66</td><td style="text-align: right;;"></td><td style=";">Jack</td><td style="text-align: right;;">49</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;"></td><td style=";">Ana</td><td style="text-align: right;;">51</td><td style="text-align: right;;"></td><td style=";">Johnson</td><td style="text-align: right;;">36</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;"></td><td style=";">Stephen</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style=";">Nicole</td><td style="text-align: right;;">34</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B19</th><td style="text-align:left">=INDEX(<font color="#0000FF">$A$2:$A$14,MATCH(<font color="#FF0000">C19,$D$2:$D$14,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E19</th><td style="text-align:left">=INDEX(<font color="#0000FF">$A$2:$A$14,MATCH(<font color="#FF0000">F19,$D$2:$D$14,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C19</th><td style="text-align:left">{=LARGE(<font color="#0000FF">IF(<font color="#FF0000">$B$2:$B$14=1,$D$2:$D$14</font>),ROW(<font color="#FF0000"></font>)-18</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F19</th><td style="text-align:left">{=LARGE(<font color="#0000FF">IF(<font color="#FF0000">$B$2:$B$14=2,$D$2:$D$14</font>),ROW(<font color="#FF0000"></font>)-18</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

MarkTheRed

New Member
Joined
Jan 5, 2017
Messages
7
Hi,

take a look at this:

Excel 2016 (Windows) 64 bit
ABCDEF
1nameTypeGrade 1Grade 2
2Ana17251
3Mark13466
4Nicole28534
5John19391
6Marcus28532
7Stephen18550
8James14133
9Jack26149
10Phil26982
11Sam14135
12Jackie26286
13Melisa14374
14Johnson28836
15
16
17
18Top 5Grade 2 T1Top 5Grade 2 T2
19John91Jackie86
20Melisa74Phil82
21Mark66Jack49
22Ana51Johnson36
23Stephen50Nicole34

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B19=INDEX($A$2:$A$14,MATCH(C19,$D$2:$D$14,0))
E19=INDEX($A$2:$A$14,MATCH(F19,$D$2:$D$14,0))

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
C19{=LARGE(IF($B$2:$B$14=1,$D$2:$D$14),ROW()-18)}
F19{=LARGE(IF($B$2:$B$14=2,$D$2:$D$14),ROW()-18)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>


Hello,

This is a marvelous solution, and thank you for that, although i am not sure it would work for me. From the looks of it, this one does not have any pivots, and the pivots are essential. Let me explain: The real data that i work with contains a lot, lot more data, and they are divided into weeks and months. The pivots are there to ensure that, for reporting purposes, we can filter the week/month that we need, and i need the formulas to match everything once the pivots are set, because they are originally intended to be used by people that have very little to no knowledge of Excel.
Apologies for omitting the week/month part, i did not think it through in detail at the time of writing the post. Also, if it means anything to you, i am using Excel 2010.

Thanks in advance, Filip
 

MarkTheRed

New Member
Joined
Jan 5, 2017
Messages
7
Hello,

This is a marvelous solution, and thank you for that, although i am not sure it would work for me. From the looks of it, this one does not have any pivots, and the pivots are essential. Let me explain: The real data that i work with contains a lot, lot more data, and they are divided into weeks and months. The pivots are there to ensure that, for reporting purposes, we can filter the week/month that we need, and i need the formulas to match everything once the pivots are set, because they are originally intended to be used by people that have very little to no knowledge of Excel.
Apologies for omitting the week/month part, i did not think it through in detail at the time of writing the post. Also, if it means anything to you, i am using Excel 2010.

Thanks in advance, Filip

After testing out a bit i see the lack of my knowledge, index match seems to be working for me, once again thank you very much for taking the time to help me out.

Kind regards, Filip
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

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