excel match feature

simon31

Board Regular
Joined
May 31, 2019
Messages
64
Hello there,

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

wcqjRUbueJkUQAAAABJRU5ErkJggg==


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
 
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
 
Last edited:
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Re: excel filter - formula

keno-results-sampelacdbecfe2ff81ed7.png


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
 
Last edited:
Upvote 0
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">
 
Upvote 0
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">
 
Upvote 0
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">
 
Upvote 0
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">
 
Upvote 0
Re: excel filter - formula

Meant to say I’m not able to resolve satisfactory
 
Upvote 0
Re: excel filter - formula

no39fd2a4e8229aa1ef.png



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. ?


one3a58280e6757b199.png


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 ?

resultstabdd780b8c3fdb927e.png
 
Last edited:
Upvote 0
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?

yourlist159e797401be89e7.png
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top