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
 
ok, this is basically the post#7 trying to do but i was just too lazy to label the columns properly


Book1
ABCDEFGHIJKLMNOPQRSTUVWX
11314223655667778
2DateTimeNo of hits
303-Jun10271015162023273134373940454950525558601
41361011151619232530313236394042454749511
53591013141718212529313237384143454850532
662567121417212328303336384044475054572
702-Jun106101318222526303438424345495457586368692
818131519222426293237384247505253555762673
9348914151620212227282930323741454851553
106571014181921232730333537394447495152552
1101-Jun1012510131719202125262931323638424446512
1218131820252931343940444548495253586266692
13310111517212326283235364145485255575861632
1468111619212328333739434447505559626367691
1531-May109131516181923273135374042434546484951521
161351011121316172125273236404246515559613
173481013141924263031364044474951556063644
1869131718232526293133374142475255565859622
1930-May1010151619222730333435373944484953575861651
2019111617192025273237394344454752535659600
21310141922262728293237414344474852576164692
2265101520252732374044454750555660626771761
2329-May10371114172126272933343539444751555861632
24156712161920222629343840414347525456611
25347914192223263034374247485257586264682
2662349141516212628323538414245475051552
2728-May108101213141517182023263134394448525355583
2818101314171819242831363839414651555962654
293791013141720222728293234353640444750534
3069101415192328323338414348515457626771721
3127-May109121314161920222629323336404244454752534
3218131618212429303132354043475153586061651
333591012161718222631353740444752535456571
346261015202427283236373944475054585964651
3526-May1024811141619232831363942454951566064672
3618131518202328323536383944454649545657622
3739141618192425283233354044495054565863641
38646811141823272833384044454751545558622
3925-May101248111314151621252732333740434752562
40110121621242931323337383944495255566061621
413691316192123252627323741434647515559642
4262789131718222428333439404142454748512
4324-May105101519232728323742464749545760646870720
44115810131819242832353839404446505560612
45324811121620222528293437424547495255592
4661256101213151823273033384246505559612
4723-May109111316182025303435394247515556596466673
481271215192225283133354044495357586063681
493491215192024293233384244455052565859630
50646813162025262934364144474951535657592
5122-May10891417202429343540424446474849515559612
5217111316212630323335384044495354576062662
53310151718192126313539424546505559626769701
5468121419202226313439434549505559626465703
Sheet1
Cell Formulas
RangeFormula
X3=SUMPRODUCT(--(ISNUMBER(MATCH(O$1:V$1,$C3:$V3,0))))
 
Last edited:
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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