Bus route matching, extract & count function

jamshoot · Sep 9, 2011

Hi

I have about 50 Bus Drivers operating along 100 routes. I want to capture how many of the 50 Bus drivers are operating some of the 100 routes. I have provide a portion of my data below .... & I need help on how to do it.
I know probably it require a function consists of match, vlookup, offset to it.
But I lack knowledge on putting this together.
Sheet1 is the Driver Route data on each driver operating route.
Sheet2 is the data I need to have a function to capture the Bus No. that operate in each route.
See the 1st example.
Can anyone help.
Cheers.

<table border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td>
</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td> <td>N</td> <td>O</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #000000; FONT-FAMILY: Arial Narrow; COLOR: #ffffff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Sheet1</td> <td style="FONT-FAMILY: Arial Narrow; COLOR: #ffffff; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: left; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Driver Route</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">S/No</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">Name</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">Bus No</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">Total Route</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">R1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">R2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">R3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">R4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">R5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">R6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">R7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">R8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">R9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold" rowspan="2">R10</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">John</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">38</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Nike</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">52</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">3</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Mick</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Sally</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">5</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Jim</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">11</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">6</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Andy</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">17</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">7</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Roy</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">15</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">8</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Richard</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">16</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">9</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Ricky</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">50</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">William</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">15</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr></tbody></table>
<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 35px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 64px"></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> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #000000; FONT-FAMILY: Arial Narrow; COLOR: #ffffff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Sheet2</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold" colspan="10">No.of Bus No. operating route</td> <td>
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Route</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Total Bus Count</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">38</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">17</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">15</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">16</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">50</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">15</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">2</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">0</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">3</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">0</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">4</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">0</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">5</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">0</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">0</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">7</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">0</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">0</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">0</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">0</td></tr></tbody></table>

Comfy · Sep 9, 2011

I couldn't figure out how to do it with excel functions so I gave up and did a UDF.

I think it's close to what your after???

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Driver Route</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><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><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="text-align: center;;">S/No</td><td style="text-align: center;;">Name</td><td style="text-align: center;;">Bus No</td><td style="text-align: center;;">Total Route</td><td style="text-align: center;;">R1</td><td style="text-align: center;;">R2</td><td style="text-align: center;;">R3</td><td style="text-align: center;;">R4</td><td style="text-align: center;;">R5</td><td style="text-align: center;;">R6</td><td style="text-align: center;;">R7</td><td style="text-align: center;;">R8</td><td style="text-align: center;;">R9</td><td style="text-align: center;;">R10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1</td><td style=";">John</td><td style="text-align: right;;">38</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2</td><td style=";">Mike</td><td style="text-align: right;;">52</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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;">7</td><td style="text-align: right;;">3</td><td style=";">Mick</td><td style="text-align: right;;">9</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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;">8</td><td style="text-align: right;;">4</td><td style=";">Sally</td><td style="text-align: right;;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</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;">9</td><td style="text-align: right;;">5</td><td style=";">Jim</td><td style="text-align: right;;">11</td><td style="text-align: right;;">8</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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;">10</td><td style="text-align: right;;">6</td><td style=";">Andy</td><td style="text-align: right;;">17</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">7</td><td style=";">Roy</td><td style="text-align: right;;">15</td><td style="text-align: right;;">9</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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="text-align: right;;">8</td><td style=";">Richard</td><td style="text-align: right;;">16</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">9</td><td style=";">Ricky</td><td style="text-align: right;;">50</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">10</td><td style=";">William</td><td style="text-align: right;;">15</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Route/Occurrence</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style=";">Total Bus Count</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">38</td><td style="text-align: right;;">7</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">38</td><td style="text-align: right;;">9</td><td style="text-align: right;;">7</td><td style="text-align: right;;">17</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">3</td><td style="text-align: right;;">38</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">4</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">5</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style=";"></td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">6</td><td style="text-align: right;;">38</td><td style="text-align: right;;">9</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">7</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">9</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style=";"></td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">8</td><td style="text-align: right;;">38</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">9</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">9</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">10</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">9</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style=";"></td><td style="text-align: right;;">9</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=BusRoute(<font color="Blue">$A4,B$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=BusRoute(<font color="Blue">$A4,C$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=BusRoute(<font color="Blue">$A4,D$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=BusRoute(<font color="Blue">$A4,E$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=BusRoute(<font color="Blue">$A4,F$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">=BusRoute(<font color="Blue">$A4,G$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H4</th><td style="text-align:left">=BusRoute(<font color="Blue">$A4,H$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I4</th><td style="text-align:left">=BusRoute(<font color="Blue">$A4,I$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J4</th><td style="text-align:left">=BusRoute(<font color="Blue">$A4,J$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K4</th><td style="text-align:left">=BusRoute(<font color="Blue">$A4,K$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L4</th><td style="text-align:left">=COUNT(<font color="Blue">$B4:$K4</font>)</td></tr></tbody></table></td></tr></table><br />

Code:

Function BusRoute(ByVal route As Long, Nth As Long, Bus As Range, Routes As Range) As Variant
 Dim i As Long, j As Long, Match As Long
 Dim BusArr As Variant, RouteArr As Variant
 
 BusArr = Bus
 RouteArr = Routes
 Match = 0
 
 For i = 1 To UBound(RouteArr)
    For j = 1 To 10
        If RouteArr(i, j) = route Then
            Match = Match + 1
            If Match = Nth Then
                BusRoute = BusArr(i, 1)
                Exit Function
            End If
        End If
    Next j
 Next i
BusRoute = ""
End Function

jamshoot · Sep 9, 2011

Comfy said:
I couldn't figure out how to do it with excel functions so I gave up and did a UDF.

I think it's close to what your after???

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;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><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Driver Route</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><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><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="text-align: center;;">S/No</td><td style="text-align: center;;">Name</td><td style="text-align: center;;">Bus No</td><td style="text-align: center;;">Total Route</td><td style="text-align: center;;">R1</td><td style="text-align: center;;">R2</td><td style="text-align: center;;">R3</td><td style="text-align: center;;">R4</td><td style="text-align: center;;">R5</td><td style="text-align: center;;">R6</td><td style="text-align: center;;">R7</td><td style="text-align: center;;">R8</td><td style="text-align: center;;">R9</td><td style="text-align: center;;">R10</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1</td><td style=";">John</td><td style="text-align: right;;">38</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2</td><td style=";">Mike</td><td style="text-align: right;;">52</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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;">7</td><td style="text-align: right;;">3</td><td style=";">Mick</td><td style="text-align: right;;">9</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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;">8</td><td style="text-align: right;;">4</td><td style=";">Sally</td><td style="text-align: right;;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</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;">9</td><td style="text-align: right;;">5</td><td style=";">Jim</td><td style="text-align: right;;">11</td><td style="text-align: right;;">8</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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;">10</td><td style="text-align: right;;">6</td><td style=";">Andy</td><td style="text-align: right;;">17</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">7</td><td style=";">Roy</td><td style="text-align: right;;">15</td><td style="text-align: right;;">9</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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="text-align: right;;">8</td><td style=";">Richard</td><td style="text-align: right;;">16</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">9</td><td style=";">Ricky</td><td style="text-align: right;;">50</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">10</td><td style=";">William</td><td style="text-align: right;;">15</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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></tbody></table>
Sheet1

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;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><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Route/Occurrence</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style=";">Total Bus Count</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">38</td><td style="text-align: right;;">7</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";">
</td><td style=";">
</td><td style=";">
</td><td style=";">
</td><td style="text-align: right;;">6</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">38</td><td style="text-align: right;;">9</td><td style="text-align: right;;">7</td><td style="text-align: right;;">17</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";">
</td><td style=";">
</td><td style=";">
</td><td style=";">
</td><td style="text-align: right;;">6</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">3</td><td style="text-align: right;;">38</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";">
</td><td style=";">
</td><td style=";">
</td><td style=";">
</td><td style="text-align: right;;">6</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">4</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";">
</td><td style=";">
</td><td style="text-align: right;;">8</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">5</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style=";">
</td><td style="text-align: right;;">9</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">6</td><td style="text-align: right;;">38</td><td style="text-align: right;;">9</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style=";">
</td><td style=";">
</td><td style="text-align: right;;">8</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">7</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">9</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style=";">
</td><td style="text-align: right;;">9</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">8</td><td style="text-align: right;;">38</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style=";">
</td><td style=";">
</td><td style="text-align: right;;">8</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">9</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">9</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style="text-align: right;;">10</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">10</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">9</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">15</td><td style=";">
</td><td style="text-align: right;;">9</td></tr></tbody></table>
Sheet2

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">B4</th><td style="text-align:left">=BusRoute($A4,B$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">C4</th><td style="text-align:left">=BusRoute($A4,C$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">D4</th><td style="text-align:left">=BusRoute($A4,D$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E4</th><td style="text-align:left">=BusRoute($A4,E$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">F4</th><td style="text-align:left">=BusRoute($A4,F$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">G4</th><td style="text-align:left">=BusRoute($A4,G$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">H4</th><td style="text-align:left">=BusRoute($A4,H$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">I4</th><td style="text-align:left">=BusRoute($A4,I$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">J4</th><td style="text-align:left">=BusRoute($A4,J$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">K4</th><td style="text-align:left">=BusRoute($A4,K$3,Sheet1!$C$5:$C$14,Sheet1!$E$5:$N$14)</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">L4</th><td style="text-align:left">=COUNT($B4:$K4)</td></tr></tbody></table></td></tr></tbody></table>

Code:

Function BusRoute(ByVal route As Long, Nth As Long, Bus As Range, Routes As Range) As Variant Dim i As Long, j As Long, Match As Long Dim BusArr As Variant, RouteArr As Variant BusArr = Bus RouteArr = Routes Match = 0 For i = 1 To UBound(RouteArr) For j = 1 To 10 If RouteArr(i, j) = route Then Match = Match + 1 If Match = Nth Then BusRoute = BusArr(i, 1) Exit Function End If End If Next j Next i BusRoute = "" End Function

Hi Comfy

Thanks for replying. May I ask where should I put this code & how to activate it

Cheers

Comfy · Sep 9, 2011

While your workbook is active press Alt+F11

In the Project window you should see your workbook name.

Right-Click this, choose Insert and then Module.

Paste the below code into it, I have made an amendment.

Code:

Function BusRoute(ByVal route As Long, Nth As Long, Bus As Range, Routes As Range) As Variant
 Dim i As Long, j As Long, Match As Long
 Dim BusArr As Variant, RouteArr As Variant
 
 BusArr = Bus
 RouteArr = Routes
 Match = 0
 
 For i = 1 To UBound(RouteArr)
    For j = 1 To UBound(RouteArr,2)
        If RouteArr(i, j) = route Then
            Match = Match + 1
            If Match = Nth Then
                BusRoute = BusArr(i, 1)
                Exit Function
            End If
        End If
    Next j
 Next i
BusRoute = ""
End Function

/Comfy

jamshoot · Sep 9, 2011

Hi Comfy

I still cannot get it work. I followed your instructions to paste the code & then copy your formula in my BusRoute sheet B4, it shows "Ambiguous name detected:BusRoute".

Is there anyone who can share to help me create a function using Index & Match method. I have search the internet - found no solution relating to my case. It seems that when a column have duplicate value, it does not work using Index & Match. It will only extract the 1st value.
Btw, I am using Excel 2003, OS:Win7

Cheers

T. Valko · Sep 9, 2011

jamshoot said:
Hi

I have about 50 Bus Drivers operating along 100 routes. I want to capture how many of the 50 Bus drivers are operating some of the 100 routes. I have provide a portion of my data below .... & I need help on how to do it.
I know probably it require a function consists of match, vlookup, offset to it.
But I lack knowledge on putting this together.
Sheet1 is the Driver Route data on each driver operating route.
Sheet2 is the data I need to have a function to capture the Bus No. that operate in each route.
See the 1st example.
Can anyone help.
Cheers.

<TABLE cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>

</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #000000; TEXT-ALIGN: left">Sheet1</TD><TD style="FONT-SIZE: 8pt; COLOR: #ffffff; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: left">Driver Route</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center" rowSpan=2>S/No</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center" rowSpan=2>Name</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center" rowSpan=2>Bus No</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center" rowSpan=2>Total Route</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" rowSpan=2>R1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" rowSpan=2>R2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" rowSpan=2>R3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" rowSpan=2>R4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" rowSpan=2>R5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" rowSpan=2>R6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" rowSpan=2>R7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" rowSpan=2>R8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" rowSpan=2>R9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" rowSpan=2>R10</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">John</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">38</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Nike</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">52</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Mick</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Sally</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Jim</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Andy</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Roy</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Richard</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Ricky</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">50</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">William</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR></TBODY></TABLE>
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 35px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>

</TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Sheet2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" colSpan=10>No.of Bus No. operating route</TD><TD>

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">Route</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">Total Bus Count</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">38</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">50</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">0</TD></TR></TBODY></TABLE>

Can you show us a few more lines of the results you expect on your sheet2?

jamshoot · Sep 10, 2011

Hi T.Valko

Thanks for replying. Hope u can help me. As I mentioned in my 1st thread, I've
100 bus route which I need manually extract the Bus No. out & put in Sheet2 report. Below is the sample.

<table border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="HEIGHT: 22px"><td style="TEXT-ALIGN: left; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Sheet1</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">S/No</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Name</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Bus No</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">R1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">R2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">R3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">R4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">R5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">R6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">R7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">R8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">R9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">R10</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">John</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">38</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">1</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">2</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">10</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Nike</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">52</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">3</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Mick</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">4</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Sally</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">1</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">2</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">5</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Jim</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">11</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">6</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Andy</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">17</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">1</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">2</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">10</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">7</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Roy</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">15</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">1</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">8</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Richard</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">16</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">1</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">2</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">10</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">9</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Ricky</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">50</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">1</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">2</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ff00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">3</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">4</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">10</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">William</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">19</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">5</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">6</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">8</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">10</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 9pt; TEXT-DECORATION: underline">Note:-</td> <td style="TEXT-ALIGN: left; FONT-FAMILY: Arial Narrow; FONT-SIZE: 9pt" colspan="13">Cell E5:N14 are all the bus route. </td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: left; FONT-FAMILY: Arial Narrow; FONT-SIZE: 9pt" colspan="13">I used value to represent the road route.</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="TEXT-ALIGN: left; FONT-FAMILY: Arial Narrow; FONT-SIZE: 9pt" colspan="13">Above I highlighted 3 routes as example.</td></tr></tbody></table>
Sheet2

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 50px"> <col style="WIDTH: 33px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 27px"> <col style="WIDTH: 64px"></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> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Sheet2</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td> <td style="FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">
</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Bus Route</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt; FONT-WEIGHT: bold" colspan="10">No.of Bus No. operating route</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">Total Bus Count</td></tr> <tr style="HEIGHT: 23px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-WEIGHT: bold">1</td> <td style="TEXT-ALIGN: center">38</td> <td style="TEXT-ALIGN: center">7</td> <td style="TEXT-ALIGN: center">17</td> <td style="TEXT-ALIGN: center">15</td> <td style="TEXT-ALIGN: center">16</td> <td style="TEXT-ALIGN: center">50</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td></tr> <tr style="HEIGHT: 23px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-WEIGHT: bold">2</td> <td style="TEXT-ALIGN: center">38</td> <td style="TEXT-ALIGN: center">9</td> <td style="TEXT-ALIGN: center">7</td> <td style="TEXT-ALIGN: center">17</td> <td style="TEXT-ALIGN: center">16</td> <td style="TEXT-ALIGN: center">50</td> <td>
</td> <td>
</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td></tr> <tr style="HEIGHT: 23px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-WEIGHT: bold">3</td> <td style="TEXT-ALIGN: center">38</td> <td style="TEXT-ALIGN: center">11</td> <td style="TEXT-ALIGN: center">17</td> <td style="TEXT-ALIGN: center">15</td> <td style="TEXT-ALIGN: center">16</td> <td style="TEXT-ALIGN: center">50</td> <td>
</td> <td>
</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">6</td></tr> <tr style="HEIGHT: 23px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-WEIGHT: bold">4</td> <td style="TEXT-ALIGN: center">38</td> <td style="TEXT-ALIGN: center">52</td> <td style="TEXT-ALIGN: center">7</td> <td style="TEXT-ALIGN: center">11</td> <td style="TEXT-ALIGN: center">17</td> <td style="TEXT-ALIGN: center">15</td> <td style="TEXT-ALIGN: center">16</td> <td style="TEXT-ALIGN: center">50</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td></tr> <tr style="HEIGHT: 23px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-WEIGHT: bold">5</td> <td style="TEXT-ALIGN: center">38</td> <td style="TEXT-ALIGN: center">52</td> <td style="TEXT-ALIGN: center">7</td> <td style="TEXT-ALIGN: center">11</td> <td style="TEXT-ALIGN: center">17</td> <td style="TEXT-ALIGN: center">15</td> <td style="TEXT-ALIGN: center">16</td> <td style="TEXT-ALIGN: center">50</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow">19</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td></tr> <tr style="HEIGHT: 23px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-WEIGHT: bold">6</td> <td style="TEXT-ALIGN: center">38</td> <td style="TEXT-ALIGN: center">9</td> <td style="TEXT-ALIGN: center">11</td> <td style="TEXT-ALIGN: center">17</td> <td style="TEXT-ALIGN: center">15</td> <td style="TEXT-ALIGN: center">16</td> <td style="TEXT-ALIGN: center">50</td> <td style="TEXT-ALIGN: center">19</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow">19</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td></tr> <tr style="HEIGHT: 23px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-WEIGHT: bold">7</td> <td style="TEXT-ALIGN: center">38</td> <td style="TEXT-ALIGN: center">52</td> <td style="TEXT-ALIGN: center">9</td> <td style="TEXT-ALIGN: center">11</td> <td style="TEXT-ALIGN: center">17</td> <td style="TEXT-ALIGN: center">15</td> <td style="TEXT-ALIGN: center">16</td> <td style="TEXT-ALIGN: center">50</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow">19</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td></tr> <tr style="HEIGHT: 25px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-WEIGHT: bold">8</td> <td style="TEXT-ALIGN: center">38</td> <td style="TEXT-ALIGN: center">7</td> <td style="TEXT-ALIGN: center">11</td> <td style="TEXT-ALIGN: center">17</td> <td style="TEXT-ALIGN: center">15</td> <td style="TEXT-ALIGN: center">16</td> <td style="TEXT-ALIGN: center">50</td> <td style="TEXT-ALIGN: center">19</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">8</td></tr> <tr style="HEIGHT: 25px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-WEIGHT: bold">9</td> <td style="TEXT-ALIGN: center">38</td> <td style="TEXT-ALIGN: center">52</td> <td style="TEXT-ALIGN: center">9</td> <td style="TEXT-ALIGN: center">7</td> <td style="TEXT-ALIGN: center">11</td> <td style="TEXT-ALIGN: center">17</td> <td style="TEXT-ALIGN: center">15</td> <td style="TEXT-ALIGN: center">16</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow">50</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow">19</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">10</td></tr> <tr style="HEIGHT: 23px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Arial Narrow; FONT-WEIGHT: bold">10</td> <td style="TEXT-ALIGN: center">38</td> <td style="TEXT-ALIGN: center">52</td> <td style="TEXT-ALIGN: center">9</td> <td style="TEXT-ALIGN: center">11</td> <td style="TEXT-ALIGN: center">17</td> <td style="TEXT-ALIGN: center">15</td> <td style="TEXT-ALIGN: center">16</td> <td style="TEXT-ALIGN: center">50</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow">19</td> <td style="FONT-FAMILY: Arial Narrow">
</td> <td style="TEXT-ALIGN: center; FONT-FAMILY: Arial Narrow; FONT-SIZE: 8pt">9</td></tr> <tr style="HEIGHT: 22px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</td> <td style="TEXT-ALIGN: left; FONT-FAMILY: Arial Narrow; FONT-SIZE: 9pt" colspan="12">I need to extract the Bus No. if they operating the Bus Route in Col A3:A12 based</td></tr> <tr style="HEIGHT: 23px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</td> <td style="TEXT-ALIGN: left; FONT-FAMILY: Arial Narrow; FONT-SIZE: 9pt" colspan="12">on Sheet1 data</td></tr></tbody></table>
Cheers

markmzz · Sep 10, 2011

Maybe this (in R4 is a array formula - use Ctrl+Shift+Enter and not only Enter):

Note: I put all data in one sheet to show all data.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #FFFF00;;">Sheet1</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><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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">Sheet2</td><td style="font-weight: bold;;">No.of Bus </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><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;">2</td><td style=";">Driver Route</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><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><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="font-weight: bold;;">No. operating route</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><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;">3</td><td style="font-weight: bold;;">S/No</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Bus No</td><td style="font-weight: bold;;">Total Route</td><td style="font-weight: bold;;">R1</td><td style="font-weight: bold;;">R2</td><td style="font-weight: bold;;">R3</td><td style="font-weight: bold;;">R4</td><td style="font-weight: bold;;">R5</td><td style="font-weight: bold;;">R6</td><td style="font-weight: bold;;">R7</td><td style="font-weight: bold;;">R8</td><td style="font-weight: bold;;">R9</td><td style="font-weight: bold;;">R10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Route</td><td style="font-weight: bold;text-align: right;;">1</td><td style="font-weight: bold;text-align: right;;">2</td><td style="font-weight: bold;text-align: right;;">3</td><td style="font-weight: bold;text-align: right;;">4</td><td style="font-weight: bold;text-align: right;;">5</td><td style="font-weight: bold;text-align: right;;">6</td><td style="font-weight: bold;text-align: right;;">7</td><td style="font-weight: bold;text-align: right;;">8</td><td style="font-weight: bold;text-align: right;;">9</td><td style="font-weight: bold;text-align: right;;">10</td><td style="font-weight: bold;;">Total Bus Count</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1</td><td style=";">John</td><td style="text-align: right;;">38</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">38</td><td style="text-align: right;;">7</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2</td><td style=";">Nike</td><td style="text-align: right;;">52</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">38</td><td style="text-align: right;;">9</td><td style="text-align: right;;">7</td><td style="text-align: right;;">17</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">3</td><td style=";">Mick</td><td style="text-align: right;;">9</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">38</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">4</td><td style=";">Sally</td><td style="text-align: right;;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</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><td style="text-align: right;;">4</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">5</td><td style=";">Jim</td><td style="text-align: right;;">11</td><td style="text-align: right;;">8</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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;;">5</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">19</td><td style=";"></td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">6</td><td style=";">Andy</td><td style="text-align: right;;">17</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">38</td><td style="text-align: right;;">9</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">19</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">7</td><td style=";">Roy</td><td style="text-align: right;;">15</td><td style="text-align: right;;">9</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">9</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">19</td><td style=";"></td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">8</td><td style=";">Richard</td><td style="text-align: right;;">16</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">38</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">19</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">9</td><td style=";">Ricky</td><td style="text-align: right;;">50</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">9</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">19</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">10</td><td style=";">William</td><td style="text-align: right;;">19</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</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><td style="text-align: right;;">10</td><td style="text-align: right;;">38</td><td style="text-align: right;;">52</td><td style="text-align: right;;">9</td><td style="text-align: right;;">11</td><td style="text-align: right;;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">50</td><td style="text-align: right;;">19</td><td style=";"></td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">14</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><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><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><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><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;">15</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">AB4</th><td style="text-align:left">=COUNT(<font color="Blue">R4:AA4</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">R4</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIF(<font color="Red">Sheet1!$E$4:$N$13,$Q4</font>)<COLUMNS(<font color="Red">$R$4:R$4</font>),"",INDEX(<font color="Red">Sheet1!$C$4:$C$13,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$E$4:$N$13=$Q4,ROW(<font color="Teal">Sheet1!$C$4:$C$13</font>)-ROW(<font color="Teal">Sheet1!$C$4</font>)+1,2^21</font>),COLUMNS(<font color="Purple">$R$4:R$4</font>)</font>)</font>)</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 />
Markmzz

T. Valko · Sep 10, 2011

jamshoot said:
Hi T.Valko

Thanks for replying. Hope u can help me. As I mentioned in my 1st thread, I've
100 bus route which I need manually extract the Bus No. out & put in Sheet2 report. Below is the sample.

<TABLE cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="HEIGHT: 22px"><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: left">Sheet1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">S/No</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">Bus No</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">R1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">R2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">R3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">R4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">R5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">R6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">R7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">R8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">R9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">R10</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">John</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">38</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ff00; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Nike</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">52</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Mick</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Sally</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Jim</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ff00; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Andy</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">17</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ff00; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Roy</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">15</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ff00; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Richard</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">16</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ff00; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">Ricky</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">50</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ff00; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">William</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">19</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center; TEXT-DECORATION: underline">Note:-</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: left" colSpan=13>Cell E5:N14 are all the bus route. </TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: left" colSpan=13>I used value to represent the road route.</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: left" colSpan=13>Above I highlighted 3 routes as example.</TD></TR></TBODY></TABLE>
Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 50px"><COL style="WIDTH: 33px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 27px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>

</TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">Sheet2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow">

</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">Bus Route</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center" colSpan=10>No.of Bus No. operating route</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">Total Bus Count</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">50</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD>

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">50</TD><TD>

</TD><TD>

</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">50</TD><TD>

</TD><TD>

</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">6</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: center">52</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: center">52</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">19</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">19</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: center">52</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">19</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">8</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: center">52</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">50</TD><TD style="FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">10</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: center">52</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">19</TD><TD style="FONT-FAMILY: Arial Narrow">

</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: center">9</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: left" colSpan=12>I need to extract the Bus No. if they operating the Bus Route in Col A3:A12 based</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial Narrow; TEXT-ALIGN: left" colSpan=12>on Sheet1 data</TD></TR></TBODY></TABLE>
Cheers

Try this...

With your data on Sheet1 in the range:

C3:C12 = Bus No
D3:M12 = Route No

On Sheet2 you have the route numbers listed A2:AA11.

In the formulas I use the following defined named ranges:

Name: Bus
Refers to: =Sheet1!$C:$C
Name: Table
Refers to: =Sheet1!$D$3:$M$12

On Sheet2 enter this array formula** in B2:

=IF(COLUMNS($B2:B2)>COUNTIF(Table,$A2),"",INDEX(Bus,SMALL(IF(Table=$A2,ROW(Table)),COLUMNS($B2:B2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to B11 then across until you get a full column of blank cells.

Here's a small sample file that demonstrates this.

zzzJamshoot.xls 33kb

http://cjoint.com/?AIkqCO7ynqm

jamshoot · Sep 10, 2011

Hi T.Valko & markmzz

A million thanks to both of u. I have tried both methods & it works great.
Esp to markmzz for your detail explanations - appreciate very much.

Cheers

Bus route matching, extract & count function

jamshoot

Board Regular

Excel Facts

Comfy

Well-known Member

jamshoot

Board Regular

Comfy

Well-known Member

jamshoot

Board Regular

T. Valko

Well-known Member

jamshoot

Board Regular

markmzz

MrExcel MVP

T. Valko

Well-known Member

jamshoot

Board Regular

Similar threads

Forum statistics

Share this page

Bus route matching, extract & count function

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

MrExcel MVP

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock