Finding Thunderball Matches

[guitarman43]

Hi there. I am new on here and also very new to Excel in fact a total beginner. My question is. I run a syndicate at work for the Thunderball and I wish to find out how many times each number has come out with the Thunderball i.e. 22 with Tball 8, 14 with Tball 3, etc etc. I have created the spreadsheet with all relevant data and have tried a lookup formula but all I get is (0).Your help would be greatly appreciated, many thanks.

 

[ukmikeb]

@Joneye

A piece of advice, Carefully read the OPs requirements in the following quotes -

I wish to find out how many times each number has come out with the Thunderball i.e. 22 with Tball 8, 14 with Tball 3, etc etc

and

I have to find how many times (say 18 has come out with Tball 6 and 37 with Tball 3

hth

 

[guitarman43]

Hi There Joneye. I must apologise for the delay in answering. But unfortunately I had an accident at work and damaged my right eye which is now all covered up so I am doing this with one eye and believe me it is very slow. Anyway I will get round to trying to do this formula as soon as they take the stiches and padding away. Many thanks for your time and trouble will speak to you soon.
Mike

 

[guitarman43]

Hi There UKMIKEB. I apologise forth delay in answering. But as I have just explained to Joneye I had an accident at work and damaged my right eye so I don't see very well at the moment its all stiched and padded over and it is a long job doing this with one eye. Anyway many thanks for your time and trouble will speak with you when stiches and padding has been removed.
Mike same name as you

 

[guitarman43]

Hi There Joneye. My mate sent me an Email with his layout of this Thunderball formula but he has done this by hand and it is exactly how I perceive my finished formula to look. As you can see each number is shown on how many appearances it has made with each Tball.It has took me ages to write this so I hope you can understand it ok. Many Thanks Mike
<table border="0" cellpadding="0" cellspacing="0" width="960"><colgroup><col style="width:48pt" span="15" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">nums</td> <td class="xl63" style="width:48pt" width="64">1</td> <td class="xl63" style="width:48pt" width="64">2</td> <td class="xl63" style="width:48pt" width="64">3</td> <td class="xl63" style="width:48pt" width="64">4</td> <td class="xl63" style="width:48pt" width="64">5</td> <td class="xl63" style="width:48pt" width="64">6</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">14Tballs</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">1</td> <td class="xl63" style="width:48pt" width="64">5</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">6</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">10</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">2</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">6</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">15</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">3</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">14</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">4</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">18</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">19</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">12</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">5</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">6</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">19</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">14</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">6</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">20</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">14</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">7</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">13</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">8</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">18</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">14</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">9</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">21</td> <td class="xl63" style="width:48pt" width="64">4</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">6</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">17</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">10</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">19</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">18</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">11</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">14</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">12</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">19</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">11</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">13</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">6</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">19</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">6</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">5</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">14</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">14</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">19</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">15</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">3</td> <td class="xl63" style="width:48pt" width="64">14</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">16</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">13</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">17</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">6</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">4</td> <td class="xl63" style="width:48pt" width="64">5</td> <td class="xl63" style="width:48pt" width="64">16</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">9</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">18</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">5</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">19</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">19</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">11</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">19</td> <td class="xl63" style="width:48pt" width="64">18</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">14</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">12</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">20</td> <td class="xl63" style="width:48pt" width="64">8</td> <td class="xl63" style="width:48pt" width="64">15</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">10</td> <td class="xl63" style="width:48pt" width="64">13</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">17</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">11</td> <td class="xl63" style="width:48pt" width="64">9</td> <td class="xl63" style="width:48pt" width="64">7</td> <td class="xl63" style="width:48pt" width="64">12</td> <td class="xl63" style="width:48pt" width="64">17</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td class="xl63" style="width:48pt" width="64">
</td> </tr> </tbody></table>

 

[guitarman43]

Hi there ukmikeb. I have had the padding taken of my eye this morning so I am seeing things clearer now. Did you see I had posted a formula for the Thunderball that my mate has done but he did it by hand, God he must have some patience, i would rather have the computer do it for me. So all I need now is the formula for doing it like his. But someone told me it cannot be done unless you do it with a Macro is that right?. Because I have not got a clue how to write a Macro. Many thanks for your time.
Mike

 

[ukmikeb]

So all I need now is the formula for doing it like his. But someone told me it cannot be done unless you do it with a Macro is that right?.

Mike

Pleased you're on the mend.

It most certainly can be done with the formulas I have given you.

Formula below (subset 1 -5 Numbers, 1 - 8 Thunderballs against your original data) if you keep it on the same sheet, but that is not advisable as you will be updating the source data twice a week.

Excel Workbook
	BE	BF	BG	BH	BI	BJ	BK	BL	BM
1		1	2	3	4	5	6	7	8
2	1	0	0	0	0	0	0	0	1
3	2	0	0	0	0	0	0	0	0
4	3	0	0	0	0	0	0	1	0
5	4	0	0	0	0	0	0	1	0
Sheet14

Excel 2007

Cell Formulas
Range	Formula
BG2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BG$1)=1))
BH2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BH$1)=1))
BI2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BI$1)=1))
BJ2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BJ$1)=1))
BK2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BK$1)=1))
BL2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BL$1)=1))
BM2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BM$1)=1))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
You just have to change the 1st Index range to Yourdatasheet!Yournumbersdatarange (Yournumbersdatarange could be a named range to reflect the updated data) and similarly for the 2nd Index range Yourdatasheet!YourTballsdatarange which could also be a named range.

I have approached it this way so that you can check on the totals by filtering on the 0s and 1s grid.

hth