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 - single cell formula ?

OK, lets say you wanted to check the 3 nos group 23, 26 & 27 within last week, you put just those 3 Nos in Row 3 and 3 in M3 that should show only 1 draw,17179 with the exact 3 Nos

<img src="https://www.pixelsbin.com/images/2019/06/25/21704c50325f5984d4.jpg" alt="21704c50325f5984d4.jpg" border="0">

and if you want to check a 4 nos group say, 18, 24, 29 & 33 again you out them in Row 3 and change M3 to 4 for 4 matches.

And, only draw 17172 matches those 4 Nos

<img src="https://www.pixelsbin.com/images/2019/06/25/22e39fb134771f8f76.jpg" alt="22e39fb134771f8f76.jpg" border="0">

Am I missing something?

 

[simon31]

Re: excel filter - single cell formula ?

Yes your latest example description is exactly correct.

But if I input 4 winning numbers, thinking that those would have come more than once , is like doing trial and error.

Now can your excel file extract all the 3 combination hits, 4 combination hits, etc if available within that draw period.?

In my example some posts ago, the combination 4 numbers 22, 31 34 and 36 had hit 3 times during the draw period between 16th June 19 to 22nd June.
But I typed those 4 winning numbers in row 3 because I scanned through manually for numbers that came more than two times.
But if I did not wish to see them by manually viewing the 20 numbers for that period, then I wanted excel to extract that information automatically, rather than using trial and error method to see which of the 4 numbers may have matched during that period and that means I would have to constantly keep typing different 4 numbers in row 3 to find out which 4 numbers group came.

So what I need is if type number 3 in the single cell formula, then excel should automatically tell me the list of 3 exact matched numbers.
The results in the process of doing that could be example as follows
Example (extraction example – not from draw results , but assumed results)
3 numbers (dummy examples and assumed results )
1) 5 44 71……… hit on 16 June
2) 24 62 69…..hit on 18 June
3) 24 62 69…..hit on 21 June
4) 33 68 79…..hit on 17 June
5) 43 45 55…..hit on 18 June
6) 24 62 69….hit on 22June

So the excel automatically extracted all the possible list of exact 3 matches as above during the 7 day period.
The one that stood out in the above example is the combination 3 group , 24 62 69 as it came 3 times during the 7 day period, so I would most likely play that group one more time before it turns cold.
So I hope the above example helps make it easier to understand what I am looking for.

 

[simon31]

Re: excel filter - single cell formula ?

 

[AlanY]

Re: excel filter - single cell formula ?

i think i'm getting closer to understand what you're after.

Let me have a think about it.

 

[AlanY]

Re: excel filter - single cell formula ?

i'm trying to work out a solution but would like to confirm something with you first.

taking the period between 16/1/2019, 22/6/2019 (about 6 months) I found none of the 3 Nos comb appeared 3 times, 16 sets did came up twice (eg 25, 49, 79).

Am I right? or you may be you can give an example

 

[AlanY]

Re: excel filter - single cell formula ?

forget about me comment re the 3 Nos groups, there are 12 sets within the last 5 months

 

[AlanY]

Re: excel filter - single cell formula ?

ok, this is what I got so far

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

Lets have a bigger range (from 16/4/2019 to 22/6/2019) for testing

1) with M3 set as 3 (3 Nos group) and click Filter, you might need to adjust the zoom on the bottom right to have the full picture

There are 67 draws that has 3 Nos matched, shown on Column AM:AQ, with the freq on Column AR

<img src="https://www.pixelsbin.com/images/2019/06/27/D18883eda9d8fed585.jpg" alt="D18883eda9d8fed585.jpg" border="0">

2) On Cell AR7, you can pick the No of freq for the matched sets, pick 4 you can see the 4 Draws with that exact 4 Nos.

<img src="https://www.pixelsbin.com/images/2019/06/27/D2d8c409a176db65b1.jpg" alt="D2d8c409a176db65b1.jpg" border="0">

3) Similar for freq of 3. showing 2 sets of the 3 Nos in 6 draws

<img src="https://www.pixelsbin.com/images/2019/06/27/D3d01ec9c8601fbed0.jpg" alt="D3d01ec9c8601fbed0.jpg" border="0">

4) You can change M4 to 4 for the 4 Nos group with freq of 3 like this

<img src="https://www.pixelsbin.com/images/2019/06/27/D467dcdbef78d945ca.jpg" alt="D467dcdbef78d945ca.jpg" border="0">

 

[simon31]

Re: excel filter - did not pick up the folowing

The above 4 set combo above hit 3 times between 16June19 to 22 June 19 which I observed manually.

But your file has missed the above 4 numbers when applying the filter ?

 

[AlanY]

Re: excel filter - did not pick up the folowing

The above 4 set combo above hit 3 times between 16June19 to 22 June 19 which I observed manually.

But your file has missed the above 4 numbers when applying the filter ?

But those set of 4 numbers are not within the 10 numbers in N4:W4!

I think I probably getting what you’re after. My first thought is it may beyond my limited knowledge of excel to solve it.

 

[AlanY]

Re: excel filter - did not pick up the folowing

i might found a way to do it.
would you confirm that you're looking for the 'hot' groups within the 20 nos each draw not the 10 nos that you're playing.

actually that made sense such that i've no idea how Keno works but i think i'd a better understanding now