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

 

[AlanY]

I start with the 8 Nos and all draws between 23/6/2018 & 22/6/2019 and click update

<img src="https://www.pixelsbin.com/images/2019/08/12/17e060cd6b49676e0.jpg" alt="17e060cd6b49676e0.jpg" border="0">

then copy the same 8 Nos in O5:U5 and pick 3 in M5 (i.e. 3 or more out of the 8 Nos matched), click check and I got 469 draws

<img src="https://www.pixelsbin.com/images/2019/08/12/212aa25cb0b37efff.jpg" alt="212aa25cb0b37efff.jpg" border="0">

similar for 4 Nos or more > 149
and 5 Nos or more > 20

and I only got 2 draws that matched 6 out of the 8 Nos from the last year.
Are you sure you got 12 hits by checking manually?

<img src="https://www.pixelsbin.com/images/2019/08/12/3b660fa276cdbb57e.jpg" alt="3b660fa276cdbb57e.jpg" border="0">

 

[simon31]

extra macro request for hot 3 only for quick execution & reduce search time

I don't remember now about the 12 hits manual check as it is nearly 2 months.


After you fixed the issue in post 119, I have never been able to get results using the hot 3 in the hot results tab as the file was busy


I ran the hot 3 tab for more than 2 hours, but it still did not finish. don't know why ?

To fix the problem, could you add an extra macro to search only the exact hot3 numbers in the hot results tab and use another macro for 4 exact matches ( and then I can input them in winning number results tab to find out the date and time the exact matches hit.

And use the current hot3 macro only for searching two and 1 set of numbers.


So by separating the macros, I could get quicker execution of the results for 3 exact matches in lesser time.


You are getting closer to helping get a formula for the exact matches in hot results tab