Check Result With Played Combinations

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Hello To All,</SPAN></SPAN>

I want to check the result database, which is given in the example below in cells Y2:AL13 with my played combinations is given in the cells B2:O13 </SPAN></SPAN>
And I want to have counted only that row combination that has minimum 10 to 14 is correct. </SPAN></SPAN>


A</SPAN></SPAN>
B</SPAN></SPAN>
C</SPAN></SPAN>
D</SPAN></SPAN>
E</SPAN></SPAN>
F</SPAN></SPAN>
G</SPAN></SPAN>
H</SPAN></SPAN>
I</SPAN></SPAN>
J</SPAN></SPAN>
K</SPAN></SPAN>
L</SPAN></SPAN>
M</SPAN></SPAN>
N</SPAN></SPAN>
O</SPAN></SPAN>
P</SPAN></SPAN>
Q</SPAN></SPAN>
R</SPAN></SPAN>
S</SPAN></SPAN>
T</SPAN></SPAN>
U</SPAN></SPAN>
V</SPAN></SPAN>
W</SPAN></SPAN>
X</SPAN></SPAN>
Y</SPAN></SPAN>
Z</SPAN></SPAN>
AA</SPAN></SPAN>
AB</SPAN></SPAN>
AC</SPAN></SPAN>
AD</SPAN></SPAN>
AE</SPAN></SPAN>
AF</SPAN></SPAN>
AG</SPAN></SPAN>
AH</SPAN></SPAN>
AI</SPAN></SPAN>
AJ</SPAN></SPAN>
AK</SPAN></SPAN>
AL</SPAN></SPAN>
1</SPAN></SPAN>
Nº Comb</SPAN></SPAN>
P1</SPAN></SPAN>
P2</SPAN></SPAN>
P3</SPAN></SPAN>
P4</SPAN></SPAN>
P5</SPAN></SPAN>
P6</SPAN></SPAN>
P7</SPAN></SPAN>
P8</SPAN></SPAN>
P9</SPAN></SPAN>
P10</SPAN></SPAN>
P11</SPAN></SPAN>
P12</SPAN></SPAN>
P13</SPAN></SPAN>
P14</SPAN></SPAN>

Result</SPAN>

10</SPAN>
11</SPAN>
12</SPAN>
13</SPAN>
14</SPAN>
Draws nº</SPAN></SPAN>
P1</SPAN></SPAN>
P2</SPAN></SPAN>
P3</SPAN></SPAN>
P4</SPAN></SPAN>
P5</SPAN></SPAN>
P6</SPAN></SPAN>
P7</SPAN></SPAN>
P8</SPAN></SPAN>
P9</SPAN></SPAN>
P10</SPAN></SPAN>
P11</SPAN></SPAN>
P12</SPAN></SPAN>
P13</SPAN></SPAN>
P14</SPAN></SPAN>
2</SPAN></SPAN>
1</SPAN></SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
1</SPAN></SPAN>
1</SPAN>
X</SPAN>
1</SPAN></SPAN>
X</SPAN></SPAN>
1</SPAN></SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>

10</SPAN>

1</SPAN></SPAN>




1</SPAN></SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
3</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN>
X</SPAN></SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN></SPAN>
1</SPAN>
1</SPAN></SPAN>
1</SPAN>
1</SPAN></SPAN>
1</SPAN>
1</SPAN>

10</SPAN>

1</SPAN></SPAN>




2</SPAN></SPAN>
2</SPAN>
2</SPAN>
2</SPAN>
2</SPAN>
2</SPAN>
X</SPAN>
X</SPAN>
X</SPAN>
2</SPAN>
1</SPAN>
X</SPAN>
X</SPAN>
2</SPAN>
1</SPAN>
4</SPAN></SPAN>
3</SPAN></SPAN>
1</SPAN></SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
2</SPAN></SPAN>
1</SPAN>
X</SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
1</SPAN></SPAN>
1</SPAN>
2</SPAN></SPAN>

10</SPAN>

1</SPAN></SPAN>




3</SPAN></SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
X</SPAN>
5</SPAN></SPAN>
4</SPAN></SPAN>
X</SPAN>
2</SPAN></SPAN>
X</SPAN>
1</SPAN>
1</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN>
1</SPAN></SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>

10</SPAN>

1</SPAN></SPAN>




4</SPAN></SPAN>
2</SPAN>
2</SPAN>
X</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
X</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
X</SPAN>
X</SPAN>
2</SPAN>
6</SPAN></SPAN>
5</SPAN></SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
2</SPAN></SPAN>
1</SPAN></SPAN>
1</SPAN>
X</SPAN>
2</SPAN></SPAN>
2</SPAN>
2</SPAN></SPAN>
1</SPAN>

10</SPAN>

1</SPAN></SPAN>




5</SPAN></SPAN>
2</SPAN>
X</SPAN>
1</SPAN>
1</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
2</SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
X</SPAN>
2</SPAN>
2</SPAN>
7</SPAN></SPAN>
6</SPAN></SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
2</SPAN></SPAN>
X</SPAN>
X</SPAN>
1</SPAN>
1</SPAN></SPAN>
X</SPAN></SPAN>
X</SPAN></SPAN>
1</SPAN>
1</SPAN>

10</SPAN>

1</SPAN></SPAN>




6</SPAN></SPAN>
X</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
2</SPAN>
2</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
2</SPAN>
2</SPAN>
8</SPAN></SPAN>
7</SPAN></SPAN>
1</SPAN></SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN></SPAN>
1</SPAN></SPAN>
X</SPAN>
2</SPAN></SPAN>
1</SPAN></SPAN>
X</SPAN></SPAN>
X</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN></SPAN>

1</SPAN></SPAN>






7</SPAN></SPAN>
1</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
9</SPAN></SPAN>
8</SPAN></SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN></SPAN>
X</SPAN>
2</SPAN></SPAN>
X</SPAN>
X</SPAN>
2</SPAN></SPAN>
1</SPAN></SPAN>
X</SPAN></SPAN>
X</SPAN></SPAN>
2</SPAN></SPAN>
1</SPAN>

4</SPAN></SPAN>






8</SPAN></SPAN>
1</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
2</SPAN>
2</SPAN>
1</SPAN>
X</SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
10</SPAN></SPAN>
9</SPAN></SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>
1</SPAN></SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>
1</SPAN></SPAN>
X</SPAN></SPAN>
X</SPAN></SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>

1</SPAN></SPAN>






9</SPAN></SPAN>
1</SPAN>
X</SPAN>
X</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
2</SPAN>
11</SPAN></SPAN>
10</SPAN></SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN></SPAN>
X</SPAN>
X</SPAN>
X</SPAN></SPAN>
1</SPAN></SPAN>
X</SPAN></SPAN>
X</SPAN></SPAN>
X</SPAN></SPAN>
2</SPAN></SPAN>

3</SPAN></SPAN>






10</SPAN></SPAN>
1</SPAN>
X</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
2</SPAN>
X</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
2</SPAN>
12</SPAN></SPAN>
11</SPAN></SPAN>
1</SPAN></SPAN>
X</SPAN></SPAN>
1</SPAN></SPAN>
2</SPAN></SPAN>
1</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN>
1</SPAN></SPAN>
1</SPAN>
2</SPAN></SPAN>
1</SPAN>
2</SPAN>
X</SPAN></SPAN>
X</SPAN></SPAN>

4</SPAN></SPAN>






11</SPAN></SPAN>
X</SPAN>
1</SPAN>
X</SPAN>
1</SPAN>
1</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
X</SPAN>
X</SPAN>
2</SPAN>
X</SPAN>
X</SPAN>
13</SPAN></SPAN>
12</SPAN></SPAN>
1</SPAN></SPAN>
2</SPAN></SPAN>
1</SPAN></SPAN>
2</SPAN></SPAN>
1</SPAN></SPAN>
X</SPAN></SPAN>
2</SPAN></SPAN>
X</SPAN>
1</SPAN>
2</SPAN></SPAN>
1</SPAN>
1</SPAN></SPAN>
X</SPAN></SPAN>
2</SPAN></SPAN>

3</SPAN></SPAN>






12</SPAN></SPAN>
X</SPAN>
1</SPAN>
1</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
1</SPAN>
2</SPAN>
1</SPAN>
2</SPAN>
X</SPAN>
1</SPAN>

<TBODY>
</TBODY>



Spreadsheet Formulas</SPAN>
Cell</SPAN></SPAN>
Formula</SPAN></SPAN>
Q2</SPAN></SPAN>
{=COUNT(SEARCH</SPAN>(B2:O2;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q3</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B3:O3;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q4</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B4:O4;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q5</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B5:O5;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q6</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B6:O6;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q7</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B7:O7;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q8</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B8:O8;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q9</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B9:O9;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q10</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B10:O10;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q11</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B11:O11;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q12</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B12:O12;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>
Q13</SPAN></SPAN>
{=COUNT(SEARCH</SPAN> (B13:O13;$Y$2:$AL$2)</SPAN>)}</SPAN></SPAN>

<TBODY>
</TBODY>

Formula Array:</SPAN>
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</SPAN>

<TBODY>
</TBODY>


Here the below is a final count of all result. Which I have calculated manually.</SPAN></SPAN>



S</SPAN></SPAN>
T</SPAN></SPAN>
U</SPAN></SPAN>
V</SPAN></SPAN>
W</SPAN></SPAN>
15</SPAN></SPAN>
10</SPAN>
11</SPAN>
12</SPAN>
13</SPAN>
14</SPAN>
16</SPAN></SPAN>
1</SPAN></SPAN>




17</SPAN></SPAN>
1</SPAN></SPAN>




18</SPAN></SPAN>
1</SPAN></SPAN>




19</SPAN></SPAN>
1</SPAN></SPAN>




20</SPAN></SPAN>
2</SPAN></SPAN>




21</SPAN></SPAN>
1</SPAN></SPAN>




22</SPAN></SPAN>
1</SPAN></SPAN>
1</SPAN></SPAN>



23</SPAN></SPAN>





24</SPAN></SPAN>
1</SPAN></SPAN>




25</SPAN></SPAN>
2</SPAN></SPAN>




26</SPAN></SPAN>
1</SPAN></SPAN>




27</SPAN></SPAN>
1</SPAN></SPAN>





<TBODY>
</TBODY>
Please is it possible to get VBA solution for this problem.</SPAN></SPAN>



Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Assuming column Q is already filled in, try the following:

Code:
Sub countResults()


    Dim i As Integer
    Dim lst As Integer
    
    lst = Range("a1").End(xlDown).Row
    
    For i = 2 To lst
        With Range("Q" & i)
            If .Value >= 10 Then
                .Offset(, .Value - 8).Value = "1"
            End If
        End With
    Next i
End Sub

Question: is the formula in column Q correct; you're checking all your combinations with row 2 of the results database.
 
Upvote 0
Thank you mvsub1 for your response.<o:p></o:p>
<o:p></o:p>
Your code is giving perfect result, checking all the combination with only row 2, but what I want all the combination should be checked with all row of database and give count result as is shown below. Which, I have calculated manually.<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
S<o:p></o:p>
T<o:p></o:p>
U<o:p></o:p>
V<o:p></o:p>
W<o:p></o:p>
1<o:p></o:p>
2<o:p></o:p>
3<o:p></o:p>
4<o:p></o:p>
5<o:p></o:p>
6<o:p></o:p>
7<o:p></o:p>
8<o:p></o:p>
9<o:p></o:p>
10<o:p></o:p>
11<o:p></o:p>
12<o:p></o:p>
13<o:p></o:p>

<TBODY> [TD="bgcolor: transparent"]
10<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
11<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
12<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
13<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
14<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
2<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
2<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]

</TBODY>
<o:p></o:p>
Formula In Cell Q2 And Copied Down is correct<o:p></o:p>
Formula Array: =COUNT(SEARCH(B2:O2,$Y$2:$AL$2))<o:p></o:p>
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
<o:p></o:p>
<o:p></o:p>
Regards,<o:p></o:p>
Kishan<o:p></o:p>
 
Upvote 0
Hi,</SPAN></SPAN>

Normally usual lottery result checker check only one result row with all lottery database, which is I am getting with my opening post.</SPAN></SPAN>

But I want that that all lottery database result could be checked with my all combination and display final result of which are correct between "10 to 14" and display the resume result in cells S2:W13 </SPAN></SPAN>

Hope now it is clearer</SPAN></SPAN>

Please Help</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Upvote 0
Please help if is it possible to have any VBA solution to get desire result</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Upvote 0
Please ignore this thread because my example is not quite clear I will start new thread with good example,</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>
 
Upvote 0

Forum statistics

Threads
1,222,569
Messages
6,166,837
Members
452,077
Latest member
hufflefry

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