excel match feature
Page 11 of 13 FirstFirst ... 910111213 LastLast
Results 101 to 110 of 122

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

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

    Default Re: excel filter - formula

    btw, have you turned off the auto calculation?

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

    Default Re: excel filter - formula

    File link below

    https://filebin.net/ap8jiv07rqryjqlf

    I have not touched any auto calculation option. I have done only the steps that was asked to be followed.

    Anyway since I have uploaded the file , you could detect if there is any descrepancy

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

    Default Re: excel filter - formula

    Quote Originally Posted by simon31 View Post
    File link below

    https://filebin.net/ap8jiv07rqryjqlf

    I have not touched any auto calculation option. I have done only the steps that was asked to be followed.

    Anyway since I have uploaded the file , you could detect if there is any descrepancy
    i think i may know what happened, just to clear if you click the update arrow on that saved file above, will you still get 12 lines of result?

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

    Default Re: excel filter - formula

    yes I clicked on the udpate arrow again on the save file and it still gives only 12 lines of results

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

    Default Re: excel filter - formula

    finally got some time to look at it again but unfortunately can't recreate the 12 lines result.
    anyway i'd revamp the file to do it differently and may not do all the things you asked for.

    https://drive.google.com/file/d/1Xty...ew?usp=sharing

    1) with the 10 winning Nos in N3:W3, let pick all the draws between 9/6/2019 to 22/6/2019 and click 'update'

    1e7982cc851228a3b.jpg

    2) that will highlighted 41 draws (Cell F5) has 3 or more matching Nos

    2d46e77e893177a14.jpg

    3) from then on you have to eyeball the hot numbers within the 41 draws, e.g. 24 & 25 look promising so you can check them by input 24 & 25 in Cells N5:O5 and click 'check'. That shows 6 draws with both 24 & 25

    3bc71680fc895c9b0.jpg

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

    Default Re: excel filter - formula

    4) again by eyeballing the results, you can try 66 and click 'check' again, that shows 2 draws with 24 25 and 66

    4ddbfae19c27b195a.jpg

    5) to check other combinations with 24 and 25, first delete 66 and click 'check' again, the same 6 lines with 24 25 here again, let say you put 7 in for checking and you get 4 draws with 24 25 & 7.

    51fc2c5d00be51673.jpg

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

    Default Re: excel filter - formula

    the steps have worked exactly the way you have shown.

    And coming back to the date period between 9th June and 22 June, by eyeballing I came to know that the 4 numbers (22, 31 34 36) hit 3 times during that date period. I was hoping that you would be able to replace eyeballing with a formula that would give the results for the most hit numbers for 4 group and 5 group numbers.

    And so eyeballing is exactly what I was trying to avoid.

    Can eyeballing be replaced with a formula to get quick display of results of most frequent 4 group and 5 group numbers ?

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

    Default Re: excel filter - formula

    I do realise what you're after, I think.

    Let me tell what I'm trying to do. with the numbers from 1 to 80 there are 82160 combinations of 3 No set.
    I can't think of any workable algorithm apart from a brute force attack, i.e. check them out 1 by 1.

    Now, for a 7 days period with 28 draws that took just over 40 mins on my computer. Well, a faster machine may cut it down to 35 or 30 mins.

    Another option is to pick a 'hot' number then let the machine check the combinations of the other 2, that cut down the calculation to 80s.
    that 'hot' number atm automatically pick out the most frequent within the draws, or can be specify by the user.

    tell me what you think.

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

    Default Re: excel filter - formula

    Quote Originally Posted by AlanY View Post
    Another option is to pick a 'hot' number then let the machine check the combinations of the other 2, that cut down the calculation to 80s.
    that 'hot' number atm automatically pick out the most frequent within the draws, or can be specify by the user.
    can you please explain with example how to do it ?

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

    Default Re: excel filter - formula

    right, here we go.

    https://drive.google.com/file/d/1bh6...ew?usp=sharing

    I'm going to show you how it works with a small sample from 16/06/2019 to 22/06/2019 3pm

    1) start with the update button you should get this, 4 out of the 7 draws has 3 or more nos matched

    153867c97e48670d5.jpg

    2) on the new 'Hot numbers' sheet there are 2 buttons with 3 options;

    a) 'Hot 3' will work out all the 3 nos groups that matched, i.e. all the 1-80 nos combinations
    b) 'Hot 2+1' similar but with 1 pre-fixed, either specify by yourself or excel will use the most frequent no within the date set.

    we will start with b) above first, with no number in Cell N5 on the front sheet, click 'Hot 2=1'
    excel will use 25 as one of the most frequent no (Col C) and work out all combinations of the other 2 (Cols A & B)
    with the results (Frequency) on Col D.
    you can see the max freq is 2 for a few combinations, 4, 38; 4, 60 etc and 25.

    36b863e18e2e12528.jpg

    3) you also pick one of your own number on N5 like this and hit 'Hot 2=1' again,

    4cc17a0b24ec855e8.jpg

    4) with another set of results like this

    55251892079821427.jpg

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
  •