Pivot Table Challenge - Arrays?

actjfc

Active Member
Joined
Jun 28, 2003
Messages
412
Excel friends,

The Pivot Below has more than 10,000 Codes. I need to find a formula or a way to get a list that looks like the summary on top.

The Description is the one corresponding to the maximum on the Total Column. I understand it can be done with Arrays. It will be a very elegant solution. I am curious if it can be solved by adding auxiliary columns to the Pivot Table Source. I appreciate any help! Thanks!


<b>Excel 2019 (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 /></colgroup><thead><tr style=" background-color: #DAE7F5 ;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">Code</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;">Description</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;">Sum(Total)</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;;">1234</td><td style="border-top: 1px solid black;;">Description 1</td><td style="text-align: right;border-top: 1px solid black;;">440,813</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-bottom: 1px solid black;;">23457</td><td style="border-bottom: 1px solid black;;">Description a</td><td style="text-align: right;border-bottom: 1px solid black;;">97,756</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;background-color: #DCE6F1;;">Total Expenses</td><td style="font-weight: bold;text-align: right;background-color: #DCE6F1;;"></td><td style="font-weight: bold;text-align: right;background-color: #DCE6F1;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;border-bottom: 1px solid black;background-color: #DCE6F1;;">Code</td><td style="font-weight: bold;border-bottom: 1px solid black;background-color: #DCE6F1;;">Description</td><td style="font-weight: bold;text-align: right;border-bottom: 1px solid black;background-color: #DCE6F1;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;border-top: 1px solid black;;">1234</td><td style="border-top: 1px solid black;;">Description 1</td><td style="text-align: right;border-top: 1px solid black;;">417,306</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 2</td><td style="text-align: right;;">7,931</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 3</td><td style="text-align: right;;">4,336</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 4</td><td style="text-align: right;;">4,229</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 5</td><td style="text-align: right;;">1,120</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 6</td><td style="text-align: right;;">927</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 7</td><td style="text-align: right;;">860</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 8</td><td style="text-align: right;;">839</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 9</td><td style="text-align: right;;">796</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 10</td><td style="text-align: right;;">599</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 11</td><td style="text-align: right;;">395</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 12</td><td style="text-align: right;;">358</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 13</td><td style="text-align: right;;">278</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 14</td><td style="text-align: right;;">245</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 15</td><td style="text-align: right;;">192</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 16</td><td style="text-align: right;;">158</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 17</td><td style="text-align: right;;">73</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 18</td><td style="text-align: right;;">69</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 19</td><td style="text-align: right;;">67</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 20</td><td style="text-align: right;;">39</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 21</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 22</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description 23</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="font-weight: bold;text-align: right;border-bottom: 1px solid black;;"></td><td style="border-bottom: 1px solid black;;">Description 24</td><td style="text-align: right;border-bottom: 1px solid black;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;">1234 Total</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">440,813</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="font-weight: bold;border-top: 1px solid black;;">23457</td><td style="border-top: 1px solid black;;">Description a</td><td style="text-align: right;border-top: 1px solid black;;">31,350</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description b</td><td style="text-align: right;;">17,529</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description c</td><td style="text-align: right;;">14,065</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description d</td><td style="text-align: right;;">8,112</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description e</td><td style="text-align: right;;">5,824</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description f</td><td style="text-align: right;;">5,473</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description g</td><td style="text-align: right;;">3,764</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description h</td><td style="text-align: right;;">3,428</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description i</td><td style="text-align: right;;">2,162</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description j</td><td style="text-align: right;;">2,117</td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="font-weight: bold;text-align: right;;"></td><td style=";">Description k</td><td style="text-align: right;;">1,972</td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="font-weight: bold;text-align: right;border-bottom: 1px solid black;;"></td><td style="border-bottom: 1px solid black;;">Description l</td><td style="text-align: right;border-bottom: 1px solid black;;">1,962</td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;;">23457 Total</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">97,756</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 />
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

actjfc

Active Member
Joined
Jun 28, 2003
Messages
412
Friends,

I was able to get a solution to my problem, and I want to share it with you, I repeated all rows in the Pivot, and used a combination of Index, Maxifs, and match, look at the formulas below, no need of arrays:

<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 /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #DCE6F1;;">Code</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #DCE6F1;;">Description</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #DCE6F1;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1234</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Description 1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 440,813 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">23457</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Description a</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 97,756 </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">C5</th><td style="text-align:left">=INDEX(<font color="#0000FF">$C$10:$C$47,MATCH(<font color="#FF0000">MAXIFS(<font color="#00FF00">$D$10:$D$47,$B$10:$B$47,B5</font>),$D$10:$D$47,0</font>),1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D5</th><td style="text-align:left">=INDEX(<font color="#0000FF">$D$10:$D$47,MATCH(<font color="#FF0000">MAXIFS(<font color="#00FF00">$D$10:$D$47,$B$10:$B$47,B5&" Total"</font>),$D$10:$D$47,0</font>),1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C6</th><td style="text-align:left">=INDEX(<font color="#0000FF">$C$10:$C$47,MATCH(<font color="#FF0000">MAXIFS(<font color="#00FF00">$D$10:$D$47,$B$10:$B$47,B6</font>),$D$10:$D$47,0</font>),1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D6</th><td style="text-align:left">=INDEX(<font color="#0000FF">$D$10:$D$47,MATCH(<font color="#FF0000">MAXIFS(<font color="#00FF00">$D$10:$D$47,$B$10:$B$47,B6&" Total"</font>),$D$10:$D$47,0</font>),1</font>)</td></tr></tbody></table></td></tr></table><br />


If you find a different solution, please post it!

Thanks!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,045
Messages
5,466,222
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top