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 - excel file

lets see if we have the same results with the attached file

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

1) with the date/time set, a dummy set of winning nos in Row 3

> click Filter

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

2) the top 10 nos are updated in Row 5

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

3) lets say you copy the top 5 nos onto Row 3 and delete the other 5 to check

> click Filter

4) that will show the 7 lines with 3 out of the 5 winning nos matched

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

5) change M3 from 3 to 4 and click filter

6) change M3 from 4 to 5 and click filter

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

 

[simon31]

Re: excel filter - excel file





the two images supporting this explanation is broken, could you please re upload those ?

 

[simon31]

Re: excel filter - excel file

It will take some time to understand your process. I will test it out today and see how the new process of 3 number , 4 number , 5 number group, etc works.

 

[simon31]

Re: excel filter - excel file




I was test trying two buddies in above snapshot . 48 has repeated twice in the same row. I don't why that has happened ? is something incorrect in the formula ?

 

[AlanY]

Re: excel filter - excel file

if you put the 4 no group in row 3 you should get the 3 hits automatically with the filter button, 3 or 4 numbers should work just as same

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

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

here are the reloaded images

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

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

 

[AlanY]

Re: excel filter - excel file

I was test trying two buddies in above snapshot . 48 has repeated twice in the same row. I don't why that has happened ? is something incorrect in the formula ?

the 2 48s are in the original data, probably a typo from source

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

 

[AlanY]

Re: excel filter - excel file

this latest version is more compact and faster and I've changed the background to make it more easy to look at the unmatched nos of the draws (if you don't like it I can revert it back)

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

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

 

[simon31]

Re: excel filter - very happy wtih new background view of hit and unhit numbers

yes this file is faster and I like the new background that is helping to view hit numbers in black and unhit numbers in white.

I am still trying understand how the 3 group, 4 group, etc work.

 

[AlanY]

Re: excel filter - very happy wtih new background view of hit and unhit numbers

it's difficult as I'm not sure how do you wanted to use the data.
lets start with a longer range for about 1 year by changing to start year to 2018

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

after clicking the filter the top 10 updated for this dates range and lets copy the top 5 in N3:R3 and clear S3:W3

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

click filter will show there are 168 draws that has any 3 out of the 5 nos matched

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

and if you clear the 2 Nos with lower frequency with the top 3, 53, 22 and 46
click filter again it shows 32 draws has those 3 Nos groups within last year.

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

 

[simon31]

Re: excel filter - single cell formula ?

Now I am beginning to understand the working of the top 10.

But let's take the sample test period (16th June 19 to 22 June 19)

What I am trying to achieve is how to make excel display 3 exact combination number matches (during the above period) or 4 exact combination number matches, 5 combination,etc.

So if you could perhaps create a formula in a single cell and when I type number 3, it should show draws of all the exact 3 combination matches atleast a minimum of 3 times (if that matching combination is drawn) And if I type 4 , all those draw numbers with exact 4 matches should be displayed if it is drawn, a minimum of 3 times in the last 7 days.

I am only looking for exact number matches(from the 20hits in the last 7days) that came maximum number of times during the given period(but atleast a minimum of 3 times and no limit on the maximum times)

If the 3, 4, 5 combination came only once in the last 7 days then I am not interested to play that and also that could mean there were no exact number for this period.

But if the exact combinations came at least 3 times or mores, I am interested in looking at the possibility of playing that combination just one more time before that combination group gets cold.