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]

Re: excel filter - formula

5) you can check any 3 Nos as before

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

6) here come the 'Hot 3' that work out the frequency of all 3 Nos group (over 80k of them).

it will take age and got lots of 3 Nos group appeared twice just within that 7 draws.
if you extend the dates/draws it will show group that appeared 3, 4 times etc

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

 

[simon31]

Re: excel filter - formula

when I click on the update button in your first steps, the excel file is busy and does not stop. so I don't know how long to wait before I know the update step is completed ?

 

[AlanY]

Re: excel filter - formula

when I click on the update button in your first steps, the excel file is busy and does not stop. so I don't know how long to wait before I know the update step is completed ?

it shouldn't.
I just downloaded the same file and check and it updates to the 4 draws instantly.

 

[simon31]

Re: excel filter - formula

the update was quick this time. That is good that the popup displays time taken for execution. the 2+1 takes approx about 128 seconds.

But when I click on hot3 icon the file runs with no end in sight. I waited for 4 minutes after clicking hot3. so how long before it shows the results of hot3.?

 

[AlanY]

Re: excel filter - formula

did warned you that it takes age!
it took 2300s (almost 40 mins) on mine that's I think you need to pick one number and use the 2+1 option

 

[simon31]

Re: excel filter - formula

ok. I understand the period selected is 16/6 to 22/6.

And the hot numbers tab return results for highest hit 3 numbers. But what I am missing is the association of the results.

I am missing the date and which times ( 10,1,3 or 6pm) the highest frequency 3 numbers hit in the hot numbers tab.

And is it possible to reduce the execution time further when clicking on hot3 to get the results faster

 

[AlanY]

Re: excel filter - formula

ok. I understand the period selected is 16/6 to 22/6.

And the hot numbers tab return results for highest hit 3 numbers. But what I am missing is the association of the results.

I am missing the date and which times ( 10,1,3 or 6pm) the highest frequency 3 numbers hit in the hot numbers tab.

And is it possible to reduce the execution time further when clicking on hot3 to get the results faster

after you got the 3 numbers groups with the "Hot3" or "Hot 2+1", you can check the associated Nos and draws by input the 3 Nos in N5:P5 as in post#111 above

 

[simon31]

Re: excel filter - formula

the hot 3 numbers search seems to be better as it gives more exact 3 number matches than 2+1.

The only downside is it has taken 3430s like you said.

One issue with the hot3 results is that it produces blank rows even though it does not have a hit when inputting the 3 nos in N5:P5.

I have attached a snapshot of two examples producing a blank row with no result as below

in above image row 17 is blank row

in above image row 17 is again a blank row. can this be fixed ?

 

[AlanY]

Re: excel filter - formula

got it fixed

https://drive.google.com/file/d/1crMZRAirKshzZfY27yP32iInTa1mPwH_/view?usp=sharing

 

[AlanY]

I should have rephrased my question but there is no option to edit once posted

13 14 22 36 55 66 77 78

I have chosen the above 8 numbers and wish to do the following.

1) find out how many times there were 8 hits,7 and 6 hits, I use countif for that


2) When I check for 6 hits of the above 8 numbers, I get about 12 times as the answer with the countif formula


3) I wish to know now which of 6 hits out of the above 8 numbers came 12 times


There could be a mix of those 6 hits that came 12 times
At the moment I spend hours to find that out .
Is there a way I can quick filter that list or use a formula for that.?

I've modified the file to do one of the first thing you asked for in the earlier post over 2 months ago!
But I didn't get close to your 12 times with 6 out of the 8 Nos in the last 12 months.

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