excel match feature

[simon31]

Hello there,

I use countif formula to find out how many times in one year 8 numbers have matched.

Usually 8 numbers match only once a year or maybe not. prize money for 8 numbers is 10k as above.

But there many times 4,5 or 6 numbers match.

For example to find out how many times 4numbers have matched, I have to scroll through for the entire year to find that out and that takes a very long time.

1) What formula can I use to find out how many times 4 numbers have matched so I can get the answer in one go.?

Each 4 numbers matched has prize money of $2.

2) what formula can I use to find out the total dollar value of the 4 numbers matched ?

https://filebin.net/kkiwhh8z3nsxif74

Thanks
simon

 

[simon31]

Re: excel filter - formula

yes I am looking for hot groups within the 20 numbers of each draw. plz do not remove the 10 numbers and it's associated frequency. If you can create a separate filter formula in another cell that will not touch the 10 numbers I m playing

 

[simon31]

Re: excel filter - formula

each keno player is allowed to play a maximum of 10 numbers out of 20 above .

tweaking your filter formula to include 20 number hits could fix the problem of some of the exact matches getting missed

https://mylotto.co.nz/results/keno

 

[AlanY]

Re: excel filter - formula

this is what I got so far

https://drive.google.com/file/d/1w1eiOcDj5qz1IVQ-N9A2cqhC2KU9CPP9/view?usp=sharing

1) Lets pick 16/4/2019 to 22/6 2019 3pm draws for testing

<img src="https://www.pixelsbin.com/images/2019/07/05/X1d21fa7c36ca21601.jpg" alt="X1d21fa7c36ca21601.jpg" border="0">

2) after you click the Update macro (arrow), on the Results sheet you should see this, all draws that has at least 2 Nos repeated 2 times or more

<img src="https://www.pixelsbin.com/images/2019/07/05/X2dfcefd8bc05f0545.jpg" alt="X2dfcefd8bc05f0545.jpg" border="0">

3) on the Results sheet, Column K shows how many Nos matched and Column L the frequency

<img src="https://www.pixelsbin.com/images/2019/07/05/X34b4a1286d00a98e7.jpg" alt="X34b4a1286d00a98e7.jpg" border="0">

4) so, if you wanted to find the 3 Nos matches, select 3 in Column K and can see 2 set of 3 Nos within this period

<img src="https://www.pixelsbin.com/images/2019/07/05/X479ef44c504234285.jpg" alt="X479ef44c504234285.jpg" border="0">

 

[AlanY]

Re: excel filter - formula

5) if you copy the second set of the 3 Nos into N3 to P3 on the front sheet, and choose 3 on M3 then click the Test macro (arrow) then you can see the 6 draws with that 3 Nos

<img src="https://www.pixelsbin.com/images/2019/07/05/X554f698028ebddbdb.jpg" alt="X554f698028ebddbdb.jpg" border="0">

6) On the Results sheet, let say you want to find the 4 Nos set, pick 4 on the filter K1, copy the 4 Nos over to the front sheet and select 4 on M3.

<img src="https://www.pixelsbin.com/images/2019/07/05/X66f254bf8799d9d6b.jpg" alt="X66f254bf8799d9d6b.jpg" border="0">

 

[AlanY]

Re: excel filter - formula

there is still one problem I'm able to solve properly yet.

1) Lets go back to the date/time range as 16/6/2019 to 22/6/2019 All draws

<img src="https://www.pixelsbin.com/images/2019/07/05/X111c1624ff5a415550.jpg" alt="X111c1624ff5a415550.jpg" border="0">

2) Click the update macro and goto the Results sheet with No of matches set as 4, you can see that your previous example of 22, 31, 34, 36 is not there!

<img src="https://www.pixelsbin.com/images/2019/07/05/X132709a87a4003fcbe.jpg" alt="X132709a87a4003fcbe.jpg" border="0">

3) the reason of that is that 4 Nos are within the 5 & 6 Nos matched, so for now if you incl. 5 &6 on the K1 filter you get this

<img src="https://www.pixelsbin.com/images/2019/07/05/X14befe85bc942f2e78.jpg" alt="X14befe85bc942f2e78.jpg" border="0">

 

[AlanY]

Re: excel filter - formula

5) So, if you copy the 2nd set of 6 into N3 etc and M3 with 6, you get the 2 draws with that exact 6 matches

<img src="https://www.pixelsbin.com/images/2019/07/05/X1555d06e325228a8fb.jpg" alt="X1555d06e325228a8fb.jpg" border="0">

6) and by changing M3 from 6 to 4, you get the 3 draws that your original example

<img src="https://www.pixelsbin.com/images/2019/07/05/X16c346ffb1f62d7d0e.jpg" alt="X16c346ffb1f62d7d0e.jpg" border="0">

 

[AlanY]

Re: excel filter - formula

Meant to say I’m not able to resolve satisfactory

 

[simon31]

Re: excel filter - formula

In the snapshot below , there is no option to select 3 in the filter, it 1,4,etc. so I am not able to see or do what you do. ?

is there a way to make cell K1 like O1 of the first tab, where I just type the value (date value). So cell K1 is at the moment drop down option. So if I could type 3 in cell K1 instead ?

 

[simon31]

Re: excel filter - formula

My results tab snapshot in the previous post has limited returns of 12 compared to yours 43 below. I don't know why?

 

[simon31]

Re: excel filter - formula

Also something is not right with the filter in cell K1. Now I see only option to select 5 and all other filter number options in cell K1 is missing