Making a top 5 list

[chris7426]

Anyone know a good formula for making a top 5 list? I have 2 rows of data:

1st row - locations
2nd row - number of problem instances

I want to just get a top 5 based on the location with the highest number of problems. The top 5 would be on a different page and would auto adjust as new problems are reported.

Thanks in advance.

 

[Yogi Anand]

Hi Chris:

I suggest you post some of your actual data along with your expected results ... so we can clearly see how your data is laid out and what you are trying to accomplish.

 

[taigovinda]

Also, if these two rows of data are a summary of your raw data, then they may not be necessary as an intermediate step. Working with the raw data might actually be a cleaner solution.

 

[chris7426]

Ok, here is a sample of data(there is 75 locations in the full list):

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Location</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Problems</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>21</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>4</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>6</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>7</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>13</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>8</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>9</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>10</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>11</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>12</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>13</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>14</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>15</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>16</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>17</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>18</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>19</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>20</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>22</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>24</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>25</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR></TBODY></TABLE>

Basically, I was to make a list that show the top 5 locations based on how many reported problems but as problems are reported, the list will auto adjust for the changes. Both the location and problems need to be listed like above

 

[Yogi Anand]

Hi Chris:

Just to make sure we are on the same page ... based on your posted data, please show your expected result and then let us take it from there.

 

[nightcrawler23]

U can use the LARGE function to generate the top 5 list

=LARGE(ur RAnge,1) will give u the TOP VALUE

U CAN USE THE LOOKUP FORMULA TO GET THE CORRESPONDING LOCATION.

 

[Jason Sidell]

This should work for what you need.

All due credit to Aladin Akyurek, as this is his formula which helps compensate for duplicate values.

Data Sheet

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 96px"><COL style="WIDTH: 141px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Location</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Problem Occurrences</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">32</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">51</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">50</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">47</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">19</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">29</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">15</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">25</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">22</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 11</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">27</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 12</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">29</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 13</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">54</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 14</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">6</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 15</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">44</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 16</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 17</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">42</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 18</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">42</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 19</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">33</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">43</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 21</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">16</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 22</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">15</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 23</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">37</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 24</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">12</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">39</TD></TR></TBODY></TABLE>

Top 5 Sheet

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 72px"><COL style="WIDTH: 96px"><COL style="WIDTH: 96px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Rank</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Location</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Occurences</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 13</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">54</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">51</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">50</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">47</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">Location 15</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccccff">44</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>{=IF(N(C2),INDEX(Data!$A$2:$A$26,SMALL(IF(Data!$B$2:$B$26=C2,ROW(Data!$B$2:$B$26)-ROW(Data!B$2)+1),COUNTIF(C$2:C2,C2))),"")}</TD></TR><TR><TD>C2</TD><TD>=IF(ROWS(C$2:C2)<=5,LARGE(Data!$B$2:$B$26,ROWS(C$2:C2)),"")</TD></TR><TR><TD>B3</TD><TD>{=IF(N(C3),INDEX(Data!$A$2:$A$26,SMALL(IF(Data!$B$2:$B$26=C3,ROW(Data!$B$2:$B$26)-ROW(Data!B$2)+1),COUNTIF(C$2:C3,C3))),"")}</TD></TR><TR><TD>C3</TD><TD>=IF(ROWS(C$2:C3)<=5,LARGE(Data!$B$2:$B$26,ROWS(C$2:C3)),"")</TD></TR><TR><TD>B4</TD><TD>{=IF(N(C4),INDEX(Data!$A$2:$A$26,SMALL(IF(Data!$B$2:$B$26=C4,ROW(Data!$B$2:$B$26)-ROW(Data!B$2)+1),COUNTIF(C$2:C4,C4))),"")}</TD></TR><TR><TD>C4</TD><TD>=IF(ROWS(C$2:C4)<=5,LARGE(Data!$B$2:$B$26,ROWS(C$2:C4)),"")</TD></TR><TR><TD>B5</TD><TD>{=IF(N(C5),INDEX(Data!$A$2:$A$26,SMALL(IF(Data!$B$2:$B$26=C5,ROW(Data!$B$2:$B$26)-ROW(Data!B$2)+1),COUNTIF(C$2:C5,C5))),"")}</TD></TR><TR><TD>C5</TD><TD>=IF(ROWS(C$2:C5)<=5,LARGE(Data!$B$2:$B$26,ROWS(C$2:C5)),"")</TD></TR><TR><TD>B6</TD><TD>{=IF(N(C6),INDEX(Data!$A$2:$A$26,SMALL(IF(Data!$B$2:$B$26=C6,ROW(Data!$B$2:$B$26)-ROW(Data!B$2)+1),COUNTIF(C$2:C6,C6))),"")}</TD></TR><TR><TD>C6</TD><TD>=IF(ROWS(C$2:C6)<=5,LARGE(Data!$B$2:$B$26,ROWS(C$2:C6)),"")</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Jason

 

[chris7426]

Thanks a lot of the help with this, works like a charm.

 

[Yogi Anand]

In addition to the solution provided by Jason Sidell, you may also find my following solution to be of some interest ...

Sheet1:

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook
	A	B
1	Location	Problem Occurrences
2	Location 1	32
3	Location 2	51
4	Location 3	50
5	Location 4	47
6	Location 5	19
7	Location 6	29
8	Location 7	15
9	Location 8	25
10	Location 9	12
11	Location 10	22
12	Location 11	27
13	Location 12	29
14	Location 13	54
15	Location 14	6
16	Location 15	44
17	Location 16	3
18	Location 17	42
19	Location 18	42
20	Location 19	33
21	Location 20	43
22	Location 21	16
23	Location 22	15
24	Location 23	37
25	Location 24	12
26	Location 25	39
Sheet1

then Sheet2:

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook
	A
1	Location Top5
2	Location 13
3	Location 2
4	Location 3
5	Location 4
6	Location 15
Sheet2

</body></html>

formula in cell A2 is copied down.