Making a top 5 list

chris7426

New Member
Joined
Dec 14, 2008
Messages
8
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
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

New Member
Joined
Dec 14, 2008
Messages
8
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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

Well-known Member
Joined
Sep 24, 2009
Messages
721
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

New Member
Joined
Mar 21, 2009
Messages
25

ADVERTISEMENT

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
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
AB
1LocationProblem Occurrences
2Location 132
3Location 251
4Location 350
5Location 447
6Location 519
7Location 629
8Location 715
9Location 825
10Location 912
11Location 1022
12Location 1127
13Location 1229
14Location 1354
15Location 146
16Location 1544
17Location 163
18Location 1742
19Location 1842
20Location 1933
21Location 2043
22Location 2116
23Location 2215
24Location 2337
25Location 2412
26Location 2539
Sheet1


then Sheet2:

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
A
1Location Top5
2Location 13
3Location 2
4Location 3
5Location 4
6Location 15
Sheet2


</body></html>

formula in cell A2 is copied down.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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