excel match feature
Page 2 of 13 FirstFirst 123412 ... LastLast
Results 11 to 20 of 122

Thread: excel match feature
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: excel match feature

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

    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



    Worksheet Formulas
    CellFormula
    X3=SUMPRODUCT(--(ISNUMBER(MATCH(O$1:V$1,$C3:$V3,0))))

    Last edited by AlanY; Jun 5th, 2019 at 02:20 AM.

  2. #12
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel match feature

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

  3. #13
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: excel match feature


  4. #14
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel match feature

    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.

  5. #15
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: excel match feature

    Quote Originally Posted by simon31 View Post
    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.

    Quote Originally Posted by simon31 View Post
    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

  6. #16
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel match feature

    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.

  7. #17
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: excel match feature

    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/1oUw...ew?usp=sharing

  8. #18
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 by simon31; Jun 11th, 2019 at 01:09 AM.

  9. #19
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default 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/1XUO...ew?usp=sharing

  10. #20
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 by simon31; Jun 11th, 2019 at 06:21 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •