Summing Duplicates

M

mlmrob

Board Regular
Joined
Sep 22, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Guys
I have a list of racehorses that are highlighted over five columns.
What I want to do is count the number of times each horse is highlighted.

<table x:str="" style="border-collapse: collapse; width: 717pt;" width="954" border="0" cellpadding="0" cellspacing="0"><col style="width: 20pt;" width="26"> <col style="width: 98pt;" width="130"> <col style="width: 21pt;" width="28" span="2"> <col style="width: 98pt;" width="131"> <col style="width: 20pt;" width="27" span="2"> <col style="width: 98pt;" width="130"> <col style="width: 21pt;" width="28" span="2"> <col style="width: 98pt;" width="130"> <col style="width: 22pt;" width="29" span="2"> <col style="width: 95pt;" width="127"> <col style="width: 21pt;" width="28" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" colspan="10" style="height: 12.75pt; width: 438pt;" x:str="2.00 JUDITH MARSHALL MEMORIAL STAKES (NURSERY HANDICAP) (4) 7f - Runners.... 12 £10100 Added - " width="583" height="17">2.00 JUDITH MARSHALL MEMORIAL STAKES (NURSERY HANDICAP) (4) 7f - Runners.... 12 £10100 Added - </td> <td class="xl24" style="width: 98pt;" width="130"> </td> <td class="xl25" style="width: 22pt;" width="29"> </td> <td class="xl25" style="width: 22pt;" width="29"> </td> <td class="xl24" style="width: 95pt;" width="127"> </td> <td class="xl25" style="width: 21pt;" width="28"> </td> <td class="xl25" style="width: 21pt;" width="28"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Horse</td> <td class="xl25">PLUS</td> <td class="xl25">Rank</td> <td class="xl24">Totalform</td> <td class="xl25">TFR</td> <td class="xl25">Rank</td> <td class="xl24">Bullet Proof</td> <td class="xl25">BP</td> <td class="xl25">Rank</td> <td class="xl24">Classform</td> <td class="xl25">CFR</td> <td class="xl25">Rank</td> <td class="xl24">Rawform</td> <td class="xl25">BRF</td> <td class="xl25">Rank</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Orientalist</td> <td class="xl27" x:num="">438</td> <td class="xl27" x:num="">1</td> <td class="xl26">Orientalist</td> <td class="xl27" x:num="">132</td> <td class="xl27" x:num="">1</td> <td class="xl26">Common Touch (IRE)</td> <td class="xl27" x:num="">144</td> <td class="xl27" x:num="">1</td> <td class="xl26">Saskias Dream</td> <td class="xl27" x:num="">89</td> <td class="xl27" x:num="">1</td> <td class="xl26">Orientalist</td> <td class="xl27" x:num="">88</td> <td class="xl27" x:num="">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Saskia's Dream</td> <td class="xl27" x:num="">386</td> <td class="xl27" x:num="">2</td> <td class="xl26">Saskias Dream</td> <td class="xl27" x:num="">111</td> <td class="xl27" x:num="">2</td> <td class="xl26">El Torbellino (IRE)</td> <td class="xl27" x:num="">108</td> <td class="xl27" x:num="">2</td> <td class="xl26">Smart Step</td> <td class="xl27" x:num="">75</td> <td class="xl27" x:num="">2</td> <td class="xl26">Enthusing (IRE)</td> <td class="xl27" x:num="">88</td> <td class="xl27" x:num="">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Enthusing</td> <td class="xl27" x:num="">374</td> <td class="xl27" x:num="">3</td> <td class="xl26">Lady Del Sol</td> <td class="xl27" x:num="">109</td> <td class="xl27" x:num="">3</td> <td class="xl26">Orientalist</td> <td class="xl27" x:num="">99</td> <td class="xl27" x:num="">3</td> <td class="xl26">Lady Del Sol</td> <td class="xl27" x:num="">75</td> <td class="xl27" x:num="">2</td> <td class="xl26">Buzz Law (IRE)</td> <td class="xl27" x:num="">88</td> <td class="xl27" x:num="">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Smart Step</td> <td class="xl27" x:num="">366</td> <td class="xl27" x:num="">4</td> <td class="xl26">Buzz Law (IRE)</td> <td class="xl27" x:num="">101</td> <td class="xl27" x:num="">4</td> <td class="xl26">Lady Del Sol</td> <td class="xl27" x:num="">75</td> <td class="xl27" x:num="">4</td> <td class="xl26">Orientalist</td> <td class="xl27" x:num="">70</td> <td class="xl27" x:num="">4</td> <td class="xl26">Common Touch (IRE)</td> <td class="xl27" x:num="">88</td> <td class="xl27" x:num="">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Common Touch</td> <td class="xl25" x:num="">361</td> <td class="xl25" x:num="">5</td> <td class="xl24">El Torbellino (IRE)</td> <td class="xl25" x:num="">85</td> <td class="xl25" x:num="">5</td> <td class="xl24">Buzz Law (IRE)</td> <td class="xl25" x:num="">72</td> <td class="xl25" x:num="">5</td> <td class="xl26">Buzz Law (IRE)</td> <td class="xl27" x:num="">70</td> <td class="xl27" x:num="">4</td> <td class="xl24">Lady Del Sol</td> <td class="xl25" x:num="">87</td> <td class="xl25" x:num="">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Lady Del Sol</td> <td class="xl25" x:num="">357</td> <td class="xl25" x:num="">6</td> <td class="xl24">Smart Step</td> <td class="xl25" x:num="">81</td> <td class="xl25" x:num="">6</td> <td class="xl24">Grazeon Again (IRE)</td> <td class="xl25" x:num="">69</td> <td class="xl25" x:num="">6</td> <td class="xl26">Common Touch (IRE)</td> <td class="xl27" x:num="">70</td> <td class="xl27" x:num="">4</td> <td class="xl24">Saskias Dream</td> <td class="xl25" x:num="">87</td> <td class="xl25" x:num="">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">El Torbellino</td> <td class="xl25" x:num="">348</td> <td class="xl25" x:num="">7</td> <td class="xl24">Common Touch (IRE)</td> <td class="xl25" x:num="">80</td> <td class="xl25" x:num="">7</td> <td class="xl24">Smart Step</td> <td class="xl25" x:num="">66</td> <td class="xl25" x:num="">7</td> <td class="xl24">Enthusing (IRE)</td> <td class="xl25" x:num="">69</td> <td class="xl25" x:num="">7</td> <td class="xl24">Last Destination (IRE)</td> <td class="xl25" x:num="">86</td> <td class="xl25" x:num="">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Piccarello</td> <td class="xl25" x:num="">327</td> <td class="xl25" x:num="">8</td> <td class="xl24">Last Destination (IRE)</td> <td class="xl25" x:num="">79</td> <td class="xl25" x:num="">8</td> <td class="xl24">Last Destination (IRE)</td> <td class="xl25" x:num="">60</td> <td class="xl25" x:num="">8</td> <td class="xl24">El Torbellino (IRE)</td> <td class="xl25" x:num="">67</td> <td class="xl25" x:num="">8</td> <td class="xl24">Smart Step</td> <td class="xl25" x:num="">85</td> <td class="xl25" x:num="">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Buzz Law</td> <td class="xl25" x:num="">325</td> <td class="xl25" x:num="">9</td> <td class="xl24">Enthusing (IRE)</td> <td class="xl25" x:num="">72</td> <td class="xl25" x:num="">9</td> <td class="xl24">Saskias Dream</td> <td class="xl25" x:num="">57</td> <td class="xl25" x:num="">9</td> <td class="xl24">Grazeon Again (IRE)</td> <td class="xl25" x:num="">67</td> <td class="xl25" x:num="">8</td> <td class="xl24">El Torbellino (IRE)</td> <td class="xl25" x:num="">84</td> <td class="xl25" x:num="">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Last Destination</td> <td class="xl25" x:num="">322</td> <td class="xl25" x:num="">10</td> <td class="xl24">Grazeon Again (IRE)</td> <td class="xl25" x:num="">72</td> <td class="xl25" x:num="">10</td> <td class="xl24">Tapis Libre</td> <td class="xl25" x:num="">57</td> <td class="xl25" x:num="">10</td> <td class="xl24">Last Destination (IRE)</td> <td class="xl25" x:num="">66</td> <td class="xl25" x:num="">10</td> <td class="xl24">Piccarello</td> <td class="xl25" x:num="">83</td> <td class="xl25" x:num="">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Grazeon Again</td> <td class="xl25" x:num="">297</td> <td class="xl25" x:num="">11</td> <td class="xl24">Tapis Libre</td> <td class="xl25" x:num="">63</td> <td class="xl25" x:num="">11</td> <td class="xl24">Piccarello</td> <td class="xl25" x:num="">51</td> <td class="xl25" x:num="">11</td> <td class="xl24">Tapis Libre</td> <td class="xl25" x:num="">66</td> <td class="xl25" x:num="">10</td> <td class="xl24">Grazeon Again (IRE)</td> <td class="xl25" x:num="">82</td> <td class="xl25" x:num="">11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Tapis Libre</td> <td class="xl25" x:num="">266</td> <td class="xl25" x:num="">12</td> <td class="xl24">Piccarello</td> <td class="xl25" x:num="">60</td> <td class="xl25" x:num="">12</td> <td class="xl24">Enthusing (IRE)</td> <td class="xl25" x:num="">27</td> <td class="xl25" x:num="">12</td> <td class="xl24">Piccarello</td> <td class="xl25" x:num="">62</td> <td class="xl25" x:num="">12</td> <td class="xl24">Tapis Libre</td> <td class="xl25" x:num="">81</td> <td class="xl25" x:num="">12</td> </tr> </tbody></table>
<table x:str="" style="border-collapse: collapse; width: 717pt;" width="954" border="0" cellpadding="0" cellspacing="0"><col style="width: 20pt;" width="26"> <col style="width: 98pt;" width="130"> <col style="width: 21pt;" width="28" span="2"> <col style="width: 98pt;" width="131"> <col style="width: 20pt;" width="27" span="2"> <col style="width: 98pt;" width="130"> <col style="width: 21pt;" width="28" span="2"> <col style="width: 98pt;" width="130"> <col style="width: 22pt;" width="29" span="2"> <col style="width: 95pt;" width="127"> <col style="width: 21pt;" width="28" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" colspan="8" style="height: 12.75pt; width: 396pt;" x:str="3.10 CONSTANT SECURITY CLAIMING STAKES (3) 1M 2f 88yds - Runners.... 12 £10100 Added - " width="527" height="17">3.10 CONSTANT SECURITY CLAIMING STAKES (3) 1M 2f 88yds - Runners.... 12 £10100 Added - </td> <td class="xl25" style="width: 21pt;" width="28"> </td> <td class="xl25" style="width: 21pt;" width="28"> </td> <td class="xl24" style="width: 98pt;" width="130"> </td> <td class="xl25" style="width: 22pt;" width="29"> </td> <td class="xl25" style="width: 22pt;" width="29"> </td> <td class="xl24" style="width: 95pt;" width="127"> </td> <td class="xl25" style="width: 21pt;" width="28"> </td> <td class="xl25" style="width: 21pt;" width="28"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Horse</td> <td class="xl25">PLUS</td> <td class="xl25">Rank</td> <td class="xl24">Totalform</td> <td class="xl25">TFR</td> <td class="xl25">Rank</td> <td class="xl24">Bullet Proof</td> <td class="xl25">BP</td> <td class="xl25">Rank</td> <td class="xl24">Classform</td> <td class="xl25">CFR</td> <td class="xl25">Rank</td> <td class="xl24">Rawform</td> <td class="xl25">BRF</td> <td class="xl25">Rank</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Majuro</td> <td class="xl27" x:num="">464</td> <td class="xl27" x:num="">1</td> <td class="xl26">JoBurg (USA)</td> <td class="xl27" x:num="">110</td> <td class="xl27" x:num="">1</td> <td class="xl26">Trip The Light</td> <td class="xl27" x:num="">114</td> <td class="xl27" x:num="">1</td> <td class="xl26">JoBurg (USA)</td> <td class="xl27" x:num="">78</td> <td class="xl27" x:num="">1</td> <td class="xl26">JoBurg (USA)</td> <td class="xl27" x:num="">91</td> <td class="xl27" x:num="">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Sunnyside Tom</td> <td class="xl27" x:num="">463</td> <td class="xl27" x:num="">2</td> <td class="xl26">Charlie Cool</td> <td class="xl27" x:num="">102</td> <td class="xl27" x:num="">2</td> <td class="xl26">Sunnyside Tom (IRE)</td> <td class="xl27" x:num="">105</td> <td class="xl27" x:num="">2</td> <td class="xl26">Sohcahtoa (IRE)</td> <td class="xl27" x:num="">76</td> <td class="xl27" x:num="">2</td> <td class="xl26">Bold Cross (IRE)</td> <td class="xl27" x:num="">88</td> <td class="xl27" x:num="">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Jo'burg</td> <td class="xl27" x:num="">462</td> <td class="xl27" x:num="">3</td> <td class="xl26">Trip The Light</td> <td class="xl27" x:num="">102</td> <td class="xl27" x:num="">3</td> <td class="xl26">JoBurg (USA)</td> <td class="xl27" x:num="">90</td> <td class="xl27" x:num="">3</td> <td class="xl26">Charlie Cool</td> <td class="xl27" x:num="">75</td> <td class="xl27" x:num="">3</td> <td class="xl26">Sohcahtoa (IRE)</td> <td class="xl27" x:num="">87</td> <td class="xl27" x:num="">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Trip The Light</td> <td class="xl27" x:num="">451</td> <td class="xl27" x:num="">4</td> <td class="xl26">Majuro (IRE)</td> <td class="xl27" x:num="">101</td> <td class="xl27" x:num="">4</td> <td class="xl26">Whipma Whopma Gate (IRE)</td> <td class="xl27" x:num="">90</td> <td class="xl27" x:num="">4</td> <td class="xl26">Trip The Light</td> <td class="xl27" x:num="">75</td> <td class="xl27" x:num="">4</td> <td class="xl26">Sunnyside Tom (IRE)</td> <td class="xl27" x:num="">86</td> <td class="xl27" x:num="">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Charlie Cool</td> <td class="xl25" x:num="">417</td> <td class="xl25" x:num="">5</td> <td class="xl24">Sunnyside Tom (IRE)</td> <td class="xl25" x:num="">98</td> <td class="xl25" x:num="">5</td> <td class="xl24">Majuro (IRE)</td> <td class="xl25" x:num="">81</td> <td class="xl25" x:num="">5</td> <td class="xl24">Majuro (IRE)</td> <td class="xl25" x:num="">69</td> <td class="xl25" x:num="">5</td> <td class="xl26">Wisemans Diamond (USA)</td> <td class="xl27" x:num="">86</td> <td class="xl27" x:num="">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Sohcahtoa</td> <td class="xl25" x:num="">389</td> <td class="xl25" x:num="">6</td> <td class="xl24">Sohcahtoa (IRE)</td> <td class="xl25" x:num="">98</td> <td class="xl25" x:num="">6</td> <td class="xl24">Sohcahtoa (IRE)</td> <td class="xl25" x:num="">78</td> <td class="xl25" x:num="">6</td> <td class="xl24">Bold Cross (IRE)</td> <td class="xl25" x:num="">69</td> <td class="xl25" x:num="">6</td> <td class="xl26">General Tufto</td> <td class="xl27" x:num="">86</td> <td class="xl27" x:num="">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">General Tufto</td> <td class="xl25" x:num="">383</td> <td class="xl25" x:num="">7</td> <td class="xl24">Wisemans Diamond (USA)</td> <td class="xl25" x:num="">87</td> <td class="xl25" x:num="">7</td> <td class="xl24">Wisemans Diamond (USA)</td> <td class="xl25" x:num="">78</td> <td class="xl25" x:num="">7</td> <td class="xl24">General Tufto</td> <td class="xl25" x:num="">68</td> <td class="xl25" x:num="">7</td> <td class="xl26">Trip The Light</td> <td class="xl27" x:num="">86</td> <td class="xl27" x:num="">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Bold Cross</td> <td class="xl25" x:num="">371</td> <td class="xl25" x:num="">8</td> <td class="xl24">General Tufto</td> <td class="xl25" x:num="">83</td> <td class="xl25" x:num="">8</td> <td class="xl24">Bold Cross (IRE)</td> <td class="xl25" x:num="">75</td> <td class="xl25" x:num="">8</td> <td class="xl24">Wisemans Diamond (USA)</td> <td class="xl25" x:num="">67</td> <td class="xl25" x:num="">8</td> <td class="xl24">Sceilin (IRE)</td> <td class="xl25" x:num="">84</td> <td class="xl25" x:num="">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Wiseman's Diamond</td> <td class="xl25" x:num="">357</td> <td class="xl25" x:num="">9</td> <td class="xl24">Bold Cross (IRE)</td> <td class="xl25" x:num="">79</td> <td class="xl25" x:num="">9</td> <td class="xl24">Autumn Harvest</td> <td class="xl25" x:num="">72</td> <td class="xl25" x:num="">9</td> <td class="xl24">Sceilin (IRE)</td> <td class="xl25" x:num="">67</td> <td class="xl25" x:num="">9</td> <td class="xl24">Majuro (IRE)</td> <td class="xl25" x:num="">82</td> <td class="xl25" x:num="">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Sceilin</td> <td class="xl25" x:num="">349</td> <td class="xl25" x:num="">10</td> <td class="xl24">Sceilin (IRE)</td> <td class="xl25" x:num="">74</td> <td class="xl25" x:num="">10</td> <td class="xl24">Sceilin (IRE)</td> <td class="xl25" x:num="">63</td> <td class="xl25" x:num="">10</td> <td class="xl24">Autumn Harvest</td> <td class="xl25" x:num="">66</td> <td class="xl25" x:num="">10</td> <td class="xl24">Autumn Harvest</td> <td class="xl25" x:num="">82</td> <td class="xl25" x:num="">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Autumn Harvest</td> <td class="xl25" x:num="">335</td> <td class="xl25" x:num="">11</td> <td class="xl24">Whipma Whopma Gate (IRE)</td> <td class="xl25" x:num="">73</td> <td class="xl25" x:num="">11</td> <td class="xl24">General Tufto</td> <td class="xl25" x:num="">63</td> <td class="xl25" x:num="">11</td> <td class="xl24">Sunnyside Tom (IRE)</td> <td class="xl25" x:num="">65</td> <td class="xl25" x:num="">11</td> <td class="xl24">Charlie Cool</td> <td class="xl25" x:num="">82</td> <td class="xl25" x:num="">11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Whipma Whopma Gate</td> <td class="xl25" x:num="">283</td> <td class="xl25" x:num="">12</td> <td class="xl24">Autumn Harvest</td> <td class="xl25" x:num="">72</td> <td class="xl25" x:num="">12</td> <td class="xl24">Charlie Cool</td> <td class="xl25" x:num="">57</td> <td class="xl25" x:num="">12</td> <td class="xl24">Whipma Whopma Gate (IRE)</td> <td class="xl25" x:num="">61</td> <td class="xl25" x:num="">12</td> <td class="xl24">Whipma Whopma Gate (IRE)</td> <td class="xl25" x:num="">82</td> <td class="xl25" x:num="">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24"> </td> <td class="xl25"> </td> <td class="xl25"> </td> <td class="xl24"> </td> <td class="xl25"> </td> <td class="xl25"> </td> <td class="xl24"> </td> <td class="xl25"> </td> <td class="xl25"> </td> <td class="xl24"> </td> <td class="xl25"> </td> <td class="xl25"> </td> <td class="xl24"> </td> <td class="xl25"> </td> <td class="xl25"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" colspan="8" style="height: 12.75pt;" x:str="3.45 PRESS FAMILY RACEDAY STAKES (HANDICAP) (2) 6f - Runners.... 18 £18000 Added - " height="17">3.45 PRESS FAMILY RACEDAY STAKES (HANDICAP) (2) 6f - Runners.... 18 £18000 Added - </td> <td class="xl25"> </td> <td class="xl25"> </td> <td class="xl24"> </td> <td class="xl25"> </td> <td class="xl25"> </td> <td class="xl24"> </td> <td class="xl25"> </td> <td class="xl25"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Horse</td> <td class="xl25">PLUS</td> <td class="xl25">Rank</td> <td class="xl24">Totalform</td> <td class="xl25">TFR</td> <td class="xl25">Rank</td> <td class="xl24">Bullet Proof</td> <td class="xl25">BP</td> <td class="xl25">Rank</td> <td class="xl24">Classform</td> <td class="xl25">CFR</td> <td class="xl25">Rank</td> <td class="xl24">Rawform</td> <td class="xl25">BRF</td> <td class="xl25">Rank</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Hitchens</td> <td class="xl27" x:num="">511</td> <td class="xl27" x:num="">1</td> <td class="xl26">Hitchens (IRE)</td> <td class="xl27" x:num="">129</td> <td class="xl27" x:num="">1</td> <td class="xl26">Thebes</td> <td class="xl27" x:num="">96</td> <td class="xl27" x:num="">1</td> <td class="xl26">Flipando (IRE)</td> <td class="xl27" x:num="">80</td> <td class="xl27" x:num="">1</td> <td class="xl26">Flipando (IRE)</td> <td class="xl27" x:num="">92</td> <td class="xl27" x:num="">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Secret Witness</td> <td class="xl27" x:num="">507</td> <td class="xl27" x:num="">2</td> <td class="xl26">Piazza San Pietro</td> <td class="xl27" x:num="">123</td> <td class="xl27" x:num="">2</td> <td class="xl26">Lowdown (IRE)</td> <td class="xl27" x:num="">96</td> <td class="xl27" x:num="">2</td> <td class="xl26">Joseph Henry</td> <td class="xl27" x:num="">80</td> <td class="xl27" x:num="">2</td> <td class="xl26">Joseph Henry</td> <td class="xl27" x:num="">92</td> <td class="xl27" x:num="">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Ghostwing</td> <td class="xl27" x:num="">502</td> <td class="xl27" x:num="">3</td> <td class="xl26">Joseph Henry</td> <td class="xl27" x:num="">122</td> <td class="xl27" x:num="">3</td> <td class="xl26">Joseph Henry</td> <td class="xl27" x:num="">93</td> <td class="xl27" x:num="">3</td> <td class="xl26">Tombi (USA)</td> <td class="xl27" x:num="">80</td> <td class="xl27" x:num="">3</td> <td class="xl26">Lowdown (IRE)</td> <td class="xl27" x:num="">91</td> <td class="xl27" x:num="">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl26">Misplaced Fortune</td> <td class="xl27" x:num="">486</td> <td class="xl27" x:num="">4</td> <td class="xl26">Valery Borzov (IRE)</td> <td class="xl27" x:num="">116</td> <td class="xl27" x:num="">4</td> <td class="xl26">Esoterica (IRE)</td> <td class="xl27" x:num="">87</td> <td class="xl27" x:num="">4</td> <td class="xl26">Hitchens (IRE)</td> <td class="xl27" x:num="">78</td> <td class="xl27" x:num="">4</td> <td class="xl26">Misplaced Fortune</td> <td class="xl27" x:num="">90</td> <td class="xl27" x:num="">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Valery Borzov</td> <td class="xl25" x:num="">483</td> <td class="xl25" x:num="">5</td> <td class="xl24">Lowdown (IRE)</td> <td class="xl25" x:num="">114</td> <td class="xl25" x:num="">5</td> <td class="xl26">Piazza San Pietro</td> <td class="xl27" x:num="">87</td> <td class="xl27" x:num="">5</td> <td class="xl26">Lowdown (IRE)</td> <td class="xl27" x:num="">78</td> <td class="xl27" x:num="">5</td> <td class="xl26">Hitchens (IRE)</td> <td class="xl27" x:num="">90</td> <td class="xl27" x:num="">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Piazza San Pietro</td> <td class="xl25" x:num="">470</td> <td class="xl25" x:num="">6</td> <td class="xl24">Flipando (IRE)</td> <td class="xl25" x:num="">113</td> <td class="xl25" x:num="">6</td> <td class="xl26">Flipando (IRE)</td> <td class="xl27" x:num="">87</td> <td class="xl27" x:num="">6</td> <td class="xl26">Five Star Junior (USA)</td> <td class="xl27" x:num="">78</td> <td class="xl27" x:num="">6</td> <td class="xl26">Secret Witness</td> <td class="xl27" x:num="">90</td> <td class="xl27" x:num="">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Discanti</td> <td class="xl25" x:num="">457</td> <td class="xl25" x:num="">7</td> <td class="xl24">Midnight Martini</td> <td class="xl25" x:num="">112</td> <td class="xl25" x:num="">7</td> <td class="xl26">Five Star Junior (USA)</td> <td class="xl27" x:num="">87</td> <td class="xl27" x:num="">7</td> <td class="xl24">Misplaced Fortune</td> <td class="xl25" x:num="">77</td> <td class="xl25" x:num="">7</td> <td class="xl26">Five Star Junior (USA)</td> <td class="xl27" x:num="">90</td> <td class="xl27" x:num="">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Shifting Star</td> <td class="xl25" x:num="">452</td> <td class="xl25" x:num="">8</td> <td class="xl24">Misplaced Fortune</td> <td class="xl25" x:num="">112</td> <td class="xl25" x:num="">8</td> <td class="xl24">Valery Borzov (IRE)</td> <td class="xl25" x:num="">84</td> <td class="xl25" x:num="">8</td> <td class="xl24">Esoterica (IRE)</td> <td class="xl25" x:num="">77</td> <td class="xl25" x:num="">8</td> <td class="xl26">Esoterica (IRE)</td> <td class="xl27" x:num="">90</td> <td class="xl27" x:num="">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Joseph Henry</td> <td class="xl25" x:num="">447</td> <td class="xl25" x:num="">9</td> <td class="xl24">Five Star Junior (USA)</td> <td class="xl25" x:num="">110</td> <td class="xl25" x:num="">9</td> <td class="xl24">Hitchens (IRE)</td> <td class="xl25" x:num="">81</td> <td class="xl25" x:num="">9</td> <td class="xl24">Piazza San Pietro</td> <td class="xl25" x:num="">77</td> <td class="xl25" x:num="">9</td> <td class="xl26">Hotham</td> <td class="xl27" x:num="">90</td> <td class="xl27" x:num="">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Abraham Lincoln</td> <td class="xl25" x:num="">442</td> <td class="xl25" x:num="">10</td> <td class="xl24">Ghostwing</td> <td class="xl25" x:num="">110</td> <td class="xl25" x:num="">10</td> <td class="xl24">Discanti (IRE)</td> <td class="xl25" x:num="">81</td> <td class="xl25" x:num="">10</td> <td class="xl24">Thebes</td> <td class="xl25" x:num="">76</td> <td class="xl25" x:num="">10</td> <td class="xl24">Piazza San Pietro</td> <td class="xl25" x:num="">89</td> <td class="xl25" x:num="">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Five Star Junior</td> <td class="xl25" x:num="">425</td> <td class="xl25" x:num="">11</td> <td class="xl24">Hotham</td> <td class="xl25" x:num="">110</td> <td class="xl25" x:num="">11</td> <td class="xl24">Shifting Star (IRE)</td> <td class="xl25" x:num="">78</td> <td class="xl25" x:num="">11</td> <td class="xl24">Shifting Star (IRE)</td> <td class="xl25" x:num="">76</td> <td class="xl25" x:num="">11</td> <td class="xl24">Ghostwing</td> <td class="xl25" x:num="">89</td> <td class="xl25" x:num="">11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Hotham</td> <td class="xl25" x:num="">417</td> <td class="xl25" x:num="">12</td> <td class="xl24">Discanti (IRE)</td> <td class="xl25" x:num="">108</td> <td class="xl25" x:num="">12</td> <td class="xl24">Midnight Martini</td> <td class="xl25" x:num="">75</td> <td class="xl25" x:num="">12</td> <td class="xl24">Abraham Lincoln (IRE)</td> <td class="xl25" x:num="">76</td> <td class="xl25" x:num="">12</td> <td class="xl24">Discanti (IRE)</td> <td class="xl25" x:num="">88</td> <td class="xl25" x:num="">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Tombi</td> <td class="xl25" x:num="">409</td> <td class="xl25" x:num="">13</td> <td class="xl24">Esoterica (IRE)</td> <td class="xl25" x:num="">107</td> <td class="xl25" x:num="">13</td> <td class="xl24">Tombi (USA)</td> <td class="xl25" x:num="">72</td> <td class="xl25" x:num="">13</td> <td class="xl24">Ghostwing</td> <td class="xl25" x:num="">76</td> <td class="xl25" x:num="">13</td> <td class="xl24">Shifting Star (IRE)</td> <td class="xl25" x:num="">88</td> <td class="xl25" x:num="">13</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Midnight Martini</td> <td class="xl25" x:num="">397</td> <td class="xl25" x:num="">14</td> <td class="xl24">Secret Witness</td> <td class="xl25" x:num="">102</td> <td class="xl25" x:num="">14</td> <td class="xl24">Ghostwing</td> <td class="xl25" x:num="">69</td> <td class="xl25" x:num="">14</td> <td class="xl24">Discanti (IRE)</td> <td class="xl25" x:num="">76</td> <td class="xl25" x:num="">14</td> <td class="xl24">Midnight Martini</td> <td class="xl25" x:num="">88</td> <td class="xl25" x:num="">14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Thebes</td> <td class="xl25" x:num="">377</td> <td class="xl25" x:num="">15</td> <td class="xl24">Thebes</td> <td class="xl25" x:num="">102</td> <td class="xl25" x:num="">15</td> <td class="xl24">Abraham Lincoln (IRE)</td> <td class="xl25" x:num="">63</td> <td class="xl25" x:num="">15</td> <td class="xl24">Hotham</td> <td class="xl25" x:num="">75</td> <td class="xl25" x:num="">15</td> <td class="xl24">Abraham Lincoln (IRE)</td> <td class="xl25" x:num="">87</td> <td class="xl25" x:num="">15</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Esoterica</td> <td class="xl25" x:num="">374</td> <td class="xl25" x:num="">16</td> <td class="xl24">Shifting Star (IRE)</td> <td class="xl25" x:num="">99</td> <td class="xl25" x:num="">16</td> <td class="xl24">Misplaced Fortune</td> <td class="xl25" x:num="">63</td> <td class="xl25" x:num="">16</td> <td class="xl24">Valery Borzov (IRE)</td> <td class="xl25" x:num="">75</td> <td class="xl25" x:num="">16</td> <td class="xl24">Thebes</td> <td class="xl25" x:num="">87</td> <td class="xl25" x:num="">16</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Lowdown</td> <td class="xl25" x:num="">318</td> <td class="xl25" x:num="">17</td> <td class="xl24">Abraham Lincoln (IRE)</td> <td class="xl25" x:num="">89</td> <td class="xl25" x:num="">17</td> <td class="xl24">Secret Witness</td> <td class="xl25" x:num="">57</td> <td class="xl25" x:num="">17</td> <td class="xl24">Midnight Martini</td> <td class="xl25" x:num="">75</td> <td class="xl25" x:num="">17</td> <td class="xl24">Valery Borzov (IRE)</td> <td class="xl25" x:num="">85</td> <td class="xl25" x:num="">17</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17"> </td> <td class="xl24">Flipando</td> <td class="xl25" x:num="">303</td> <td class="xl25" x:num="">18</td> <td class="xl24">Tombi (USA)</td> <td class="xl25" x:num="">87</td> <td class="xl25" x:num="">18</td> <td class="xl24">Hotham</td> <td class="xl25" x:num="">30</td> <td class="xl25" x:num="">18</td> <td class="xl24">Secret Witness</td> <td class="xl25" x:num="">74</td> <td class="xl25" x:num="">18</td> <td class="xl24">Tombi (USA)</td> <td class="xl25" x:num="">84</td> <td class="xl25" x:num="">18</td> </tr> </tbody></table>
This can trail down as far as around 700 rows with obviously different groups as each race is highlighted individually.

Thanks very much.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Hi mlmrob,

you wanna analyse races? I've changed a script that I used before. I hope it works.

Copy this code into a modul and change the name "MySheetName" into your data worksheet name. So a new worksheet "result" will be generate within all your horses, times they run and counting the rank.

<pre style='border:thin solid #9B9B9B; padding:12px 24px; margin-left:12px; color:#1B3636; overflow:auto; '><span style='color:#0000EE'>Option</span> <span style='color:#0000EE'>Explicit</span>

<span style='color:#0000EE'>Public</span> <span style='color:#0000EE'>Sub</span> SumScores()
<span style='color:#0000EE'>Dim</span> ws <span style='color:#0000EE'>As</span> Worksheet
<span style='color:#0000EE'>Dim</span> bSwitch <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Boolean</span>
<span style='color:#0000EE'>Dim</span> vValues <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Variant</span>
<span style='color:#0000EE'>Dim</span> lRow <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Long</span>
<span style='color:#0000EE'>Dim</span> lColumn <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Long</span>
<span style='color:#0000EE'>Dim</span> vText <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Variant</span>
<span style='color:#0000EE'>Dim</span> vRank <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Variant</span>
<span style='color:#0000EE'>Dim</span> vLines <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Variant</span>
<span style='color:#0000EE'>Dim</span> oHorses <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Object</span>
<span style='color:#0000EE'>Dim</span> vEntry <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Variant</span>
<span style='color:#0000EE'>Dim</span> vMax <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Long</span>

<span style='color:#0000EE'>For</span> <span style='color:#0000EE'>Each</span> ws <span style='color:#0000EE'>In</span> ThisWorkbook.Worksheets
<span style='color:#0000EE'>If</span> ws.Name = <span style='color:#FF3E3E'>"result"</span> <span style='color:#0000EE'>Then</span>
bSwitch = <span style='color:#0000EE'>True</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>Next</span> ws

<span style='color:#0000EE'>If</span> bSwitch <span style='color:#0000EE'>Then</span>
<span style='color:#0000EE'>Set</span> ws = ThisWorkbook.Worksheets(<span style='color:#FF3E3E'>"result"</span>)
ws.UsedRange.ClearContents
<span style='color:#0000EE'>Else</span>
<span style='color:#0000EE'>With</span> ThisWorkbook
.Worksheets.Add after:=.Worksheets(.Worksheets.Count)
<span style='color:#0000EE'>Set</span> ws = .Worksheets(.Worksheets.Count)
ws.Name = <span style='color:#FF3E3E'>"result"</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>With</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>

<span style='color:#0000EE'>Set</span> oHorses = CreateObject(<span style='color:#FF3E3E'>"Scripting.Dictionary"</span>)

<span style='color:#0000EE'>With</span> ThisWorkbook.Worksheets(<span style='color:#FF3E3E'>"MySheetName"</span>) <span style='color:#008000'>'need to change </span>
vValues = .UsedRange.Value
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>With</span>

<span style='color:#0000EE'>For</span> lRow = <span style='color:#0000EE'>Lbound</span>(vValues, <span style='color:#DDAA00'>1</span>) <span style='color:#0000EE'>To</span> <span style='color:#0000EE'>Ubound</span>(vValues, <span style='color:#DDAA00'>1</span>)
<span style='color:#0000EE'>For</span> lColumn = <span style='color:#0000EE'>Lbound</span>(vValues, <span style='color:#DDAA00'>2</span>) <span style='color:#0000EE'>To</span> <span style='color:#0000EE'>Ubound</span>(vValues, <span style='color:#DDAA00'>2</span>)
vText = vValues(lRow, lColumn)
<span style='color:#0000EE'>If</span> TypeName(vText) = <span style='color:#FF3E3E'>"String"</span> <span style='color:#0000EE'>Then</span>
<span style='color:#0000EE'>If</span> Len(vText) > <span style='color:#DDAA00'>3</span> <span style='color:#0000EE'>Then</span>
<span style='color:#0000EE'>If</span> lColumn + <span style='color:#DDAA00'>2</span> <= <span style='color:#0000EE'>Ubound</span>(vValues, <span style='color:#DDAA00'>2</span>) <span style='color:#0000EE'>Then</span>
vRank = vValues(lRow, lColumn + <span style='color:#DDAA00'>2</span>)
<span style='color:#0000EE'>If</span> IsNumeric(vRank) <span style='color:#0000EE'>Then</span>
vRank = <span style='color:#0000EE'>CLng</span>(vRank)
<span style='color:#0000EE'>If</span> vRank > <span style='color:#DDAA00'>0</span> <span style='color:#0000EE'>Then</span>
<span style='color:#0000EE'>If</span> vRank + <span style='color:#DDAA00'>2</span> > vMax <span style='color:#0000EE'>Then</span>
vMax = vRank + <span style='color:#DDAA00'>2</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>

<span style='color:#0000EE'>If</span> oHorses.exists(vText) <span style='color:#0000EE'>Then</span>
vLines = oHorses(vText)
<span style='color:#0000EE'>If</span> vRank + <span style='color:#DDAA00'>2</span> > <span style='color:#0000EE'>Ubound</span>(vLines) <span style='color:#0000EE'>Then</span>
<span style='color:#0000EE'>Redim</span> <span style='color:#0000EE'>Preserve</span> vLines(<span style='color:#DDAA00'>1</span> <span style='color:#0000EE'>To</span> vRank + <span style='color:#DDAA00'>2</span>)
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
vLines(<span style='color:#DDAA00'>2</span>) = vLines(<span style='color:#DDAA00'>2</span>) + <span style='color:#DDAA00'>1</span>
vLines(vRank + <span style='color:#DDAA00'>1</span>) = vLines(vRank + <span style='color:#DDAA00'>1</span>) + <span style='color:#DDAA00'>1</span>
<span style='color:#0000EE'>Else</span>
<span style='color:#0000EE'>Redim</span> vLines(<span style='color:#DDAA00'>1</span> <span style='color:#0000EE'>To</span> vRank + <span style='color:#DDAA00'>2</span>)
vLines(<span style='color:#DDAA00'>1</span>) = vText
vLines(<span style='color:#DDAA00'>2</span>) = <span style='color:#DDAA00'>1</span>
vLines(vRank + <span style='color:#DDAA00'>1</span>) = <span style='color:#DDAA00'>1</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
oHorses(vText) = vLines
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>Next</span> lColumn
<span style='color:#0000EE'>Next</span> lRow

lRow = <span style='color:#DDAA00'>1</span>
ws.Cells(<span style='color:#DDAA00'>1</span>, <span style='color:#DDAA00'>1</span>) = <span style='color:#FF3E3E'>"Name"</span>
ws.Cells(<span style='color:#DDAA00'>1</span>, <span style='color:#DDAA00'>2</span>) = <span style='color:#FF3E3E'>"Sum"</span>
<span style='color:#0000EE'>For</span> lColumn = <span style='color:#DDAA00'>3</span> <span style='color:#0000EE'>To</span> vMax
ws.Cells(<span style='color:#DDAA00'>1</span>, lColumn) = <span style='color:#FF3E3E'>"rank "</span> & lColumn - <span style='color:#DDAA00'>2</span>
<span style='color:#0000EE'>Next</span> lColumn

<span style='color:#0000EE'>For</span> <span style='color:#0000EE'>Each</span> vEntry <span style='color:#0000EE'>In</span> oHorses.items
lRow = lRow + <span style='color:#DDAA00'>1</span>
ws.Cells(lRow, <span style='color:#DDAA00'>1</span>).Resize(<span style='color:#DDAA00'>1</span>, <span style='color:#0000EE'>Ubound</span>(vEntry)).Value = vEntry
<span style='color:#0000EE'>Next</span> vEntry

<span style='color:#0000EE'>Erase</span> vLines: <span style='color:#0000EE'>Erase</span> vValues
<span style='color:#0000EE'>Set</span> ws = Nothing: <span style='color:#0000EE'>Set</span> vValues = <span style='color:#0000EE'>Nothing</span>
<span style='color:#0000EE'>Set</span> vText = Nothing: <span style='color:#0000EE'>Set</span> vRank = <span style='color:#0000EE'>Nothing</span>
<span style='color:#0000EE'>Set</span> vLines = Nothing: <span style='color:#0000EE'>Set</span> oHorses = <span style='color:#0000EE'>Nothing</span>
<span style='color:#0000EE'>Set</span> vEntry = <span style='color:#0000EE'>Nothing</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>Sub</span></pre>

Hope you will enjoy the script

Best regards
Gerd
 
Upvote 0
I'm sorry. There is a fault in the script. So please take this one
<pre style='border:thin solid #9B9B9B; padding:12px 24px; margin-left:12px; color:#1B3636; overflow:auto; '><span style='color:#0000EE'>Option</span> <span style='color:#0000EE'>Explicit</span>

<span style='color:#0000EE'>Public</span> <span style='color:#0000EE'>Sub</span> SumScores()
<span style='color:#0000EE'>Dim</span> ws <span style='color:#0000EE'>As</span> Worksheet
<span style='color:#0000EE'>Dim</span> bSwitch <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Boolean</span>
<span style='color:#0000EE'>Dim</span> vValues <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Variant</span>
<span style='color:#0000EE'>Dim</span> lRow <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Long</span>
<span style='color:#0000EE'>Dim</span> lColumn <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Long</span>
<span style='color:#0000EE'>Dim</span> vText <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Variant</span>
<span style='color:#0000EE'>Dim</span> vRank <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Variant</span>
<span style='color:#0000EE'>Dim</span> vLines <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Variant</span>
<span style='color:#0000EE'>Dim</span> oHorses <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Object</span>
<span style='color:#0000EE'>Dim</span> vEntry <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Variant</span>
<span style='color:#0000EE'>Dim</span> vMax <span style='color:#0000EE'>As</span> <span style='color:#0000EE'>Long</span>

<span style='color:#0000EE'>For</span> <span style='color:#0000EE'>Each</span> ws <span style='color:#0000EE'>In</span> ThisWorkbook.Worksheets
<span style='color:#0000EE'>If</span> ws.Name = <span style='color:#FF3E3E'>"result"</span> <span style='color:#0000EE'>Then</span>
bSwitch = <span style='color:#0000EE'>True</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>Next</span> ws

<span style='color:#0000EE'>If</span> bSwitch <span style='color:#0000EE'>Then</span>
<span style='color:#0000EE'>Set</span> ws = ThisWorkbook.Worksheets(<span style='color:#FF3E3E'>"result"</span>)
ws.UsedRange.ClearContents
<span style='color:#0000EE'>Else</span>
<span style='color:#0000EE'>With</span> ThisWorkbook
.Worksheets.Add after:=.Worksheets(.Worksheets.Count)
<span style='color:#0000EE'>Set</span> ws = .Worksheets(.Worksheets.Count)
ws.Name = <span style='color:#FF3E3E'>"result"</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>With</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>

<span style='color:#0000EE'>Set</span> oHorses = CreateObject(<span style='color:#FF3E3E'>"Scripting.Dictionary"</span>)

<span style='color:#0000EE'>With</span> ThisWorkbook.Worksheets(<span style='color:#FF3E3E'>"MySheetName"</span>) <span style='color:#008000'>'need to change </span>
vValues = .UsedRange.Value
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>With</span>

<span style='color:#0000EE'>For</span> lRow = <span style='color:#0000EE'>Lbound</span>(vValues, <span style='color:#DDAA00'>1</span>) <span style='color:#0000EE'>To</span> <span style='color:#0000EE'>Ubound</span>(vValues, <span style='color:#DDAA00'>1</span>)
<span style='color:#0000EE'>For</span> lColumn = <span style='color:#0000EE'>Lbound</span>(vValues, <span style='color:#DDAA00'>2</span>) <span style='color:#0000EE'>To</span> <span style='color:#0000EE'>Ubound</span>(vValues, <span style='color:#DDAA00'>2</span>)
vText = vValues(lRow, lColumn)
<span style='color:#0000EE'>If</span> TypeName(vText) = <span style='color:#FF3E3E'>"String"</span> <span style='color:#0000EE'>Then</span>
<span style='color:#0000EE'>If</span> Len(vText) > <span style='color:#DDAA00'>3</span> <span style='color:#0000EE'>Then</span>
<span style='color:#0000EE'>If</span> lColumn + <span style='color:#DDAA00'>2</span> <= <span style='color:#0000EE'>Ubound</span>(vValues, <span style='color:#DDAA00'>2</span>) <span style='color:#0000EE'>Then</span>
vRank = vValues(lRow, lColumn + <span style='color:#DDAA00'>2</span>)
<span style='color:#0000EE'>If</span> IsNumeric(vRank) <span style='color:#0000EE'>Then</span>
vRank = <span style='color:#0000EE'>CLng</span>(vRank)
<span style='color:#0000EE'>If</span> vRank > <span style='color:#DDAA00'>0</span> <span style='color:#0000EE'>Then</span>
<span style='color:#0000EE'>If</span> vRank + <span style='color:#DDAA00'>2</span> > vMax <span style='color:#0000EE'>Then</span>
vMax = vRank + <span style='color:#DDAA00'>2</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>

<span style='color:#0000EE'>If</span> oHorses.exists(vText) <span style='color:#0000EE'>Then</span>
vLines = oHorses(vText)
<span style='color:#0000EE'>If</span> vRank + <span style='color:#DDAA00'>2</span> > <span style='color:#0000EE'>Ubound</span>(vLines) <span style='color:#0000EE'>Then</span>
<span style='color:#0000EE'>Redim</span> <span style='color:#0000EE'>Preserve</span> vLines(<span style='color:#DDAA00'>1</span> <span style='color:#0000EE'>To</span> vRank + <span style='color:#DDAA00'>2</span>)
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
vLines(<span style='color:#DDAA00'>2</span>) = vLines(<span style='color:#DDAA00'>2</span>) + <span style='color:#DDAA00'>1</span>
vLines(vRank + <span style='color:#DDAA00'>2</span>) = vLines(vRank + <span style='color:#DDAA00'>2</span>) + <span style='color:#DDAA00'>1</span>
<span style='color:#0000EE'>Else</span>
<span style='color:#0000EE'>Redim</span> vLines(<span style='color:#DDAA00'>1</span> <span style='color:#0000EE'>To</span> vRank + <span style='color:#DDAA00'>2</span>)
vLines(<span style='color:#DDAA00'>1</span>) = vText
vLines(<span style='color:#DDAA00'>2</span>) = <span style='color:#DDAA00'>1</span>
vLines(vRank + <span style='color:#DDAA00'>2</span>) = <span style='color:#DDAA00'>1</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
oHorses(vText) = vLines
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>If</span>
<span style='color:#0000EE'>Next</span> lColumn
<span style='color:#0000EE'>Next</span> lRow

lRow = <span style='color:#DDAA00'>1</span>
ws.Cells(<span style='color:#DDAA00'>1</span>, <span style='color:#DDAA00'>1</span>) = <span style='color:#FF3E3E'>"Name"</span>
ws.Cells(<span style='color:#DDAA00'>1</span>, <span style='color:#DDAA00'>2</span>) = <span style='color:#FF3E3E'>"Sum"</span>
<span style='color:#0000EE'>For</span> lColumn = <span style='color:#DDAA00'>3</span> <span style='color:#0000EE'>To</span> vMax
ws.Cells(<span style='color:#DDAA00'>1</span>, lColumn) = <span style='color:#FF3E3E'>"rank "</span> & lColumn - <span style='color:#DDAA00'>2</span>
<span style='color:#0000EE'>Next</span> lColumn

<span style='color:#0000EE'>For</span> <span style='color:#0000EE'>Each</span> vEntry <span style='color:#0000EE'>In</span> oHorses.items
lRow = lRow + <span style='color:#DDAA00'>1</span>
ws.Cells(lRow, <span style='color:#DDAA00'>1</span>).Resize(<span style='color:#DDAA00'>1</span>, <span style='color:#0000EE'>Ubound</span>(vEntry)).Value = vEntry
<span style='color:#0000EE'>Next</span> vEntry

<span style='color:#0000EE'>Erase</span> vLines: <span style='color:#0000EE'>Erase</span> vValues
<span style='color:#0000EE'>Set</span> ws = Nothing: <span style='color:#0000EE'>Set</span> vValues = <span style='color:#0000EE'>Nothing</span>
<span style='color:#0000EE'>Set</span> vText = Nothing: <span style='color:#0000EE'>Set</span> vRank = <span style='color:#0000EE'>Nothing</span>
<span style='color:#0000EE'>Set</span> vLines = Nothing: <span style='color:#0000EE'>Set</span> oHorses = <span style='color:#0000EE'>Nothing</span>
<span style='color:#0000EE'>Set</span> vEntry = <span style='color:#0000EE'>Nothing</span>
<span style='color:#0000EE'>End</span> <span style='color:#0000EE'>Sub</span></pre>
 
Upvote 0
Thanks will give it a go. very much appreciated your time and help.
 
Upvote 0
Hi mate

Thanks for that but cant get it to work even changing the name.
 
Upvote 0
Hi mate

yeah

I get run time error '9'

Subscript out of range
 
Upvote 0
Hi Bamberg

Thanks very much for your time and patience, it is very much appreciated.
if you still interested in horseracing you can have the daily link to pick up the ratings

This is the line that you asked for

With ThisWorkbook.Worksheets("September 5th") 'need to change
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
A
  • Question Question
exel 2002 total accumulated payments
Replies
4
Views
730
anon125
A
G
  • Question Question
copy/paster to another worksheet
Replies
5
Views
779
Fluff
Fluff
C
  • Question Question
Sum
Replies
3
Views
1K
pbornemeier
pbornemeier
M
  • Question Question
Extract values "before after up down arrow" or "not arrow"
Replies
3
Views
911
Tetra201
Tetra201

Forum statistics

Threads
1,224,566
Messages
6,179,555
Members
452,928
Latest member
101blockchains

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top