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]

ok, this is basically the post#7 trying to do but i was just too lazy to label the columns properly

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															13	14	22	36	55	66	77	78
2	Date	Time																						No of hits
3	03-Jun	10	2	7	10	15	16	20	23	27	31	34	37	39	40	45	49	50	52	55	58	60		1
4		1	3	6	10	11	15	16	19	23	25	30	31	32	36	39	40	42	45	47	49	51		1
5		3	5	9	10	13	14	17	18	21	25	29	31	32	37	38	41	43	45	48	50	53		2
6		6	2	5	6	7	12	14	17	21	23	28	30	33	36	38	40	44	47	50	54	57		2
7	02-Jun	10	6	10	13	18	22	25	26	30	34	38	42	43	45	49	54	57	58	63	68	69		2
8		1	8	13	15	19	22	24	26	29	32	37	38	42	47	50	52	53	55	57	62	67		3
9		3	4	8	9	14	15	16	20	21	22	27	28	29	30	32	37	41	45	48	51	55		3
10		6	5	7	10	14	18	19	21	23	27	30	33	35	37	39	44	47	49	51	52	55		2
11	01-Jun	10	1	2	5	10	13	17	19	20	21	25	26	29	31	32	36	38	42	44	46	51		2
12		1	8	13	18	20	25	29	31	34	39	40	44	45	48	49	52	53	58	62	66	69		2
13		3	10	11	15	17	21	23	26	28	32	35	36	41	45	48	52	55	57	58	61	63		2
14		6	8	11	16	19	21	23	28	33	37	39	43	44	47	50	55	59	62	63	67	69		1
15	31-May	10	9	13	15	16	18	19	23	27	31	35	37	40	42	43	45	46	48	49	51	52		1
16		1	3	5	10	11	12	13	16	17	21	25	27	32	36	40	42	46	51	55	59	61		3
17		3	4	8	10	13	14	19	24	26	30	31	36	40	44	47	49	51	55	60	63	64		4
18		6	9	13	17	18	23	25	26	29	31	33	37	41	42	47	52	55	56	58	59	62		2
19	30-May	10	10	15	16	19	22	27	30	33	34	35	37	39	44	48	49	53	57	58	61	65		1
20		1	9	11	16	17	19	20	25	27	32	37	39	43	44	45	47	52	53	56	59	60		0
21		3	10	14	19	22	26	27	28	29	32	37	41	43	44	47	48	52	57	61	64	69		2
22		6	5	10	15	20	25	27	32	37	40	44	45	47	50	55	56	60	62	67	71	76		1
23	29-May	10	3	7	11	14	17	21	26	27	29	33	34	35	39	44	47	51	55	58	61	63		2
24		1	5	6	7	12	16	19	20	22	26	29	34	38	40	41	43	47	52	54	56	61		1
25		3	4	7	9	14	19	22	23	26	30	34	37	42	47	48	52	57	58	62	64	68		2
26		6	2	3	4	9	14	15	16	21	26	28	32	35	38	41	42	45	47	50	51	55		2
27	28-May	10	8	10	12	13	14	15	17	18	20	23	26	31	34	39	44	48	52	53	55	58		3
28		1	8	10	13	14	17	18	19	24	28	31	36	38	39	41	46	51	55	59	62	65		4
29		3	7	9	10	13	14	17	20	22	27	28	29	32	34	35	36	40	44	47	50	53		4
30		6	9	10	14	15	19	23	28	32	33	38	41	43	48	51	54	57	62	67	71	72		1
31	27-May	10	9	12	13	14	16	19	20	22	26	29	32	33	36	40	42	44	45	47	52	53		4
32		1	8	13	16	18	21	24	29	30	31	32	35	40	43	47	51	53	58	60	61	65		1
33		3	5	9	10	12	16	17	18	22	26	31	35	37	40	44	47	52	53	54	56	57		1
34		6	2	6	10	15	20	24	27	28	32	36	37	39	44	47	50	54	58	59	64	65		1
35	26-May	10	2	4	8	11	14	16	19	23	28	31	36	39	42	45	49	51	56	60	64	67		2
36		1	8	13	15	18	20	23	28	32	35	36	38	39	44	45	46	49	54	56	57	62		2
37		3	9	14	16	18	19	24	25	28	32	33	35	40	44	49	50	54	56	58	63	64		1
38		6	4	6	8	11	14	18	23	27	28	33	38	40	44	45	47	51	54	55	58	62		2
39	25-May	10	1	2	4	8	11	13	14	15	16	21	25	27	32	33	37	40	43	47	52	56		2
40		1	10	12	16	21	24	29	31	32	33	37	38	39	44	49	52	55	56	60	61	62		1
41		3	6	9	13	16	19	21	23	25	26	27	32	37	41	43	46	47	51	55	59	64		2
42		6	2	7	8	9	13	17	18	22	24	28	33	34	39	40	41	42	45	47	48	51		2
43	24-May	10	5	10	15	19	23	27	28	32	37	42	46	47	49	54	57	60	64	68	70	72		0
44		1	1	5	8	10	13	18	19	24	28	32	35	38	39	40	44	46	50	55	60	61		2
45		3	2	4	8	11	12	16	20	22	25	28	29	34	37	42	45	47	49	52	55	59		2
46		6	1	2	5	6	10	12	13	15	18	23	27	30	33	38	42	46	50	55	59	61		2
47	23-May	10	9	11	13	16	18	20	25	30	34	35	39	42	47	51	55	56	59	64	66	67		3
48		1	2	7	12	15	19	22	25	28	31	33	35	40	44	49	53	57	58	60	63	68		1
49		3	4	9	12	15	19	20	24	29	32	33	38	42	44	45	50	52	56	58	59	63		0
50		6	4	6	8	13	16	20	25	26	29	34	36	41	44	47	49	51	53	56	57	59		2
51	22-May	10	8	9	14	17	20	24	29	34	35	40	42	44	46	47	48	49	51	55	59	61		2
52		1	7	11	13	16	21	26	30	32	33	35	38	40	44	49	53	54	57	60	62	66		2
53		3	10	15	17	18	19	21	26	31	35	39	42	45	46	50	55	59	62	67	69	70		1
54		6	8	12	14	19	20	22	26	31	34	39	43	45	49	50	55	59	62	64	65	70		3
Sheet1

Cell Formulas
Range	Formula
X3	=SUMPRODUCT(--(ISNUMBER(MATCH(O$1:V$1,$C3:$V3,0))))

 

[simon31]

could you please attach excel file with the new layout.?

 

[AlanY]

here it is

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

 

[simon31]

thanks for that. How does the Y column work though ? sometimes it is reading 9 and 10 what are those values?


I was hoping for a filter column wherein if I chose the 8 numbers for a period of 3months
13 14 22 36 55 66 77 78


then the rest of the 12 numbers would have got blanked out , when I used the filter.

 

[AlanY]

thanks for that. How does the Y column work though ? sometimes it is reading 9 and 10 what are those values?


I was hoping for a filter column wherein if I chose the 8 numbers for a period of 3months
13 14 22 36 55 66 77 78


then the rest of the 12 numbers would have got blanked out , when I used the filter.

I'm still not clear of the requirements, unfortunately.

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

to help understand, would you share the original excel file of 2) above.
may be then we can get to the bottom of it

 

[simon31]

Your table is good for x column hits.
What I am still missing is the filter column. I have attached snapshot how it should be after filter.

https://filebin.net/5xfs0z4e661l7jq3

If you could add a filter option on the Y column that could filter numbers I choose in row 1.
At the moment there are 8 numbers in row one, but I could input less than 8 numbers to see the filtered numbers say from one week to 1 year.
When numbers are filtered , I would also get to know on which dates those numbers hit.
I would then also get to know how many times those numbers came over a period of time, so that I then choose the next set of numbers carefully while playing keno.

 

[AlanY]

this method uses conditional formatting.
all the numbers started with white font on white background.
when they matched the 8 numbers in O1:V1 the conditional format changes them to black font on white.


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

 

[simon31]

Re: excel number filter and unfilter

Thanks for that .

But how do I unfilter to view the 20 numbers again.

Could you please add a unfilter option to the conditional formatting so I can switch back and forth between filtered numbers now in row1( which you have already donea) but also getting the 20 numbers back again.

unfiltered data

https://filebin.net/i9a81u08cnq1h1ey

 

[AlanY]

Re: excel number filter and unfilter

all I can think of is to have 2 sheets, sheet 2 is just a direct copy of sheet 1 (i.e. changes on Sheet1 will automatically reflected on Sheet 2) but with the matched numbers filtered out.

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

 

[simon31]

Re: excel number filter and unfilter

ok I understand

The numbers in row one are 13 14 22 36 55 66 77 78.

Now I deleted the all numbers except 36 55 in row1, Because I wanted to view how many times the pair 36 and 55 appeared, unfortunately the unpaired numbers 36 and 55 appeared as well. I did not want to know how many times 36 appeared by itself or 55 appeared by itself.

All I want to view is only the number pairs 36 and 55 , so how do I filter and achieve that in Sheet2 ?

https://filebin.net/nt2txntct20f9ht5. snapshot of paired numbers and unpaired number.