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 number filter and unfilter

ok, what to do is select 2 on the column X filter, like this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1																		36	55
13	01-Jun	3	10	11	15	17	21	23	26	28	32	35	36	41	45	48	52	55	57	58	61	63		2
16	31-May	1	3	5	10	11	12	13	16	17	21	25	27	32	36	40	42	46	51	55	59	61		2
17	31-May	3	4	8	10	13	14	19	24	26	30	31	36	40	44	47	49	51	55	60	63	64		2
28	28-May	1	8	10	13	14	17	18	19	24	28	31	36	38	39	41	46	51	55	59	62	65		2
Sheet1

and sheet2 will show just the paired ones

 

[AlanY]

Re: excel number filter and unfilter

like this

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

 

[simon31]

Re: excel number filter and unfilter

Thanks for that.

The old file still shows 36 and 55 on its own as well along with the pairs in sheet2. I am not able to see (only pairs) what you see in sheet2 in your above snaphsot.

And if I try to filter your old file, the filter options are limited only for filtering upto two numbers, which means if I want to filter 5 numbers, 6 numbers or 7 numbers out of 8 that option is missing in the old file.

Can you please upload the modified file with those options. ?

Also how do I paste a snapshot onto this post please like you did above.? I have to always add snapshot as an attachment.

 

[AlanY]

Re: excel number filter and unfilter

I'd amend the data to test, select the filter in Cell Z1

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

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

 

[simon31]

Re: excel number filter and unfilter

In your updated file , I don't see a filter option in cell Z1 if that's what you meant?

Now I was doing a test for the pair 50 and 60 and so whatever I did in Sheet1 did not automatically reflect in Sheet 2

Wasn't that how you had set it up?

So the test tried was to find the pair 50 and 60 in Sheet2.

So In Sheet1, I deleted all the numbers in row1 and typed two fresh numbers 50 and 60, while the filter worked in sheet1 , it did not automatically reflect in Sheet2.
Do I have to reflect the pair 50 and 60 manually in Sheet2 by filtering the 50 and 60 there as well ?

https://ibb.co/vZ7sBTD ( sheet2)

And as usual I have been unable to upload a simple snapshot , rather I am forced to add a link to the snapshot.

The insert image option only points to a url but does nothing and does not insert image into the post, its so frustrating.

 

[simon31]

Re: excel number filter and unfilter

finally able to upload the snapshot using external website.

In Sheet2 , row 5 does not show 50 and 60 as pair , when I change the info in Sheet1. The pair 50 and 60 hit on 3rJune though and only shows the number 50 , instead of the pair 50 and 60

 

[AlanY]

Re: excel number filter and unfilter

you need to select 2 on the filter in Cell X1, on both sheets

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

 

[simon31]

excel filter - using source keno file to update the current file

Thanks for that. Now it is clear that I have to edit both sheets, sheet1 and Sheets2 for filter to work.

But the file is not going to be helpful if I cannot update the results automatically to current date.

I have the source keno results file (1994 to June 2019). Your latest excel file needs to be updated with the keno results file from 1994 to 2019.

The Four draws (10am,1pm,3pm,6pm ) started on 25 Aug 2014, before that there were two keno draws per day.

Can you please setup a formula that will automate your latest excel file from 1994 to 2019 and will populate those results automatically , so I don’t need to type them everyday.

I want to use filter and check out certain numbers right from 1994.
Also I need an option to choose a start date and end date, when I use the filter.

Source keno results file link : https://filebin.net/p0rdsf6e98opezel

 

[AlanY]

Re: excel filter - using source keno file to update the current file

I've modified the file with macros for easier to use.
You have to enable contents or similar in order to work properly

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

The highlighted input cells are
1) O5:V5 are the 8 winning numbers,
2) Dropdown lists for dates range from P2:R2 to T2:V2,
3) M5 is number of matches, and
4) M2 is the draw number to check.

There are 4 macros assigned for 3 buttons and 1 arrow
Filter button is the filtered out the matched numbers from the selections, dates and No of matches.
Reset button is to reset all.
New Data button is to add 4 lines for the latest draw.
Arrow button is to show the selected draw in M2 only.

Note that there are 3 hidden columns on the right for the calculations.

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

 

[RossTattersall]

If you're only interested in cases where it's the majority being equal, you could try

COUNTIF(

#VALUE!