+ Reply to Thread
Results 1 to 14 of 14

Finding 5 consecutive numbers in a set of 7 numbers

  1. #1
    Registered User
    Join Date
    05-12-2015
    Location
    Chiang Mai, Thailand
    MS-Off Ver
    14.3.5
    Posts
    8

    Finding 5 consecutive numbers in a set of 7 numbers

    I am trying to make a version of poker on excel but I am unable to create a way to check if the players have a straight or not after all cards are "dealt" (they are dealt with random number generators). I found a way to check if all numbers are consecutive which goes somewhat like AND(A1=B1+1,B1=C1+1, and so on. But I cannot find a quick way to apply this to all possible ways the numbers could form straights. Unless I write out every possible combination. Is there a way to make all combinations visible or just a way to speed up the process?

  2. #2
    Forum Contributor jayajaya_4's Avatar
    Join Date
    05-09-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    259

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    u can follow this link for ur solution...hope it helps

    http://www.ozgrid.com/forum/showthread.php?t=163479

  3. #3
    Forum Contributor
    Join Date
    09-02-2013
    Location
    Netherlands
    MS-Off Ver
    Office 365 (2013)
    Posts
    268

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    Ok this was a nice question.

    In VBA you can:
    - sort the 7 cards (as integers, make sure they count from 1 till 13)
    - while sorting, set duplicates as "99" or something
    so if you have the cards: Ace, Ace, 5, 6, 7, 8, 9 it should give:
    1, 5, 6, 7, 8, 9, 99

    - Then just apply these rules:
    --> 7th number - 3th number = 4 or
    --> 6th number - 2th number = 4 or
    --> 5th number - 1st number = 4 or
    --> ( 5th number - 1st number = 12 and 2nd number - 1st number = 9 ) (Hence the 1 can also be counted after the king (13 - 1 = 12)

    Hopefully I didn't make any mistakes, but most of the times when I do something, the pro's here give way better solutions xD
    Inveniam Viam Aut Faciam

  4. #4
    Registered User
    Join Date
    05-12-2015
    Location
    Chiang Mai, Thailand
    MS-Off Ver
    14.3.5
    Posts
    8

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    This is a solution to finding consecutive numbers but it does not work on doing it to check every combination, if I interpreted what you are saying correctly, I would still have to list out every combination of 5, which adds up to around 2500 combinations.

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    Hi.

    For the non-poker initiates, could you give a few examples together with your expected results?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    An Excel array formula to solve de problem if the data is in A1:A7 is

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If data is in A1:G1 then the formula becomes

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Don’t forget Ctrl + Shift + Enter

    Best regards

  7. #7
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    Sorry. The previous post did not consider duplicate values.
    The following formula already considered.

    =OR(AND(IFERROR(MATCH(SMALL(A1:A7,1)+ROW(1:5)-1,(dado),0),0)>0),IFERROR(MATCH(SMALL(A1:A7,IF(SMALL(A1:A7,1)=SMALL(A1:A7,2),3,2))+ROW(1:5)-1,(dado),0),0)>0)

    Regards.

  8. #8
    Registered User
    Join Date
    05-12-2015
    Location
    Chiang Mai, Thailand
    MS-Off Ver
    14.3.5
    Posts
    8

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    This does not seem to work for me when I inputted it into my spreadsheet. Even if there is a set of consecutive numbers it returns as false. I am not sure why this is.

  9. #9
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    Sorry. The formula include a named range dado and other small error. dado=A1:A7

    Try this

    =OR(IFERROR(AND(MATCH(SMALL(A1:A7,1)+ROW(1:5)-1,A1:A7,0)>0),FALSE),IFERROR(AND(MATCH(SMALL(A1:A7,2)+
    ROW(1:5)-1,A1:A7,0)>0),FALSE),IFERROR(AND(MATCH(SMALL(A1:A7,3)+ROW(1:5)-1,A1:A7,0)>0),FALSE))

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    don't forget Ctrl + Shift + End

    Regards
    Last edited by José Augusto; 05-19-2015 at 03:56 AM.

  10. #10
    Forum Contributor
    Join Date
    09-02-2013
    Location
    Netherlands
    MS-Off Ver
    Office 365 (2013)
    Posts
    268

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    This is what I've done.
    I made a code where I give strCards(1) till strCards(7) the numbers 1 to 52. (4 sets of 13)

    First I devide the values by 13 so that I get the real value of the card (ex. 52 = 0, => 13 "king" ... and ex2. 14 = 1 "ace" )
    Next I sorted the values and removed doubles by putting them on the end with value 99

    To see if there is a straight I figured it has to be from 1 - 5, 2 - 6 or 3 - 7. Since they add up 1 each time, the results should be 4.
    Except when the ace is the first card. (ace, 10, jack, queen, king) by sorting the cards.
    There you get 13 - 1 = 12 AND 10 - 1 = 9. That way also this is covered

    I'm sure my code can be improved A LOT... but this is just how I did it,... and it works

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    05-12-2015
    Location
    Chiang Mai, Thailand
    MS-Off Ver
    14.3.5
    Posts
    8

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    What I did to create the cards was make random generators with RANDBETWEEN(1,14) and then applied suits by going RANDBETWEEN(1,4). So two cells would represent a single card. But the suits do not matter in this scenario so I am simply looking for the card number to go up. So I do not think the difference of 4 applies to my set up. So how do I input this into my document? And I am not sure if your solution will work because of the fact that the numbers will be arranged differently, for example let's say in slot 1,2,3,4,5 the numbers 1,2,3,4, and 5 are there respectively, but if it is rearranged to 1,3,4,2,5 or something like that I'm not sure if your formula would work. And I cannot make it automatically sort the data, so I would need to account for all possible permutations.

  12. #12
    Registered User
    Join Date
    05-12-2015
    Location
    Chiang Mai, Thailand
    MS-Off Ver
    14.3.5
    Posts
    8

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    This definitely changed something because now it always says it is true instead of false. Again, I do not know if I applied it correctly, does it work for you when you do it in your spreadsheet?

  13. #13
    Forum Contributor
    Join Date
    09-02-2013
    Location
    Netherlands
    MS-Off Ver
    Office 365 (2013)
    Posts
    268

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    Just wondering... how to you make sure the random values are unique? And do you apply code to stop random formulas from auto-updating?

    - edit
    The array formula (((SO USE CTR + SHIFT + ENTER))) José gives works for straights from 2 and up... but won't see the 14, 2, 3, 4 ,5 combination.
    If you can add that in the formula, then it's all done
    Last edited by Evolta; 05-20-2015 at 01:53 AM.

  14. #14
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Finding 5 consecutive numbers in a set of 7 numbers

    Hi Evolta

    The proposed issue is "Finding 5 consecutive numbers in a set of seven numbers."
    I considered Ace = 1 or 14 Jack = 11; Queen = 12; King = 13.

    To simplify the formula I set the Seq name as
    =MATCH($A$1:$A$7,ROW(A1:A14)+IF(ROW(A1: A14)>7,3,0),0)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Thus the desired formula is
    =OR(IFERROR(AND(MATCH(SMALL(Seq,1)+ROW(1:5)-1,Seq,0)>0),FALSE),
    IFERROR(AND(MATCH(SMALL(Seq,2)+ROW(1:5)-1,Seq,0)>0),FALSE),
    IFERROR(AND(MATCH(SMALL(Seq,3)+ROW(1:5)-1,Seq,0)>0),FALSE),
    AND(MATCH(ROW(1:5)+IF(ROW(1:5)=1,13,0),A1:A7,0)))
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Best regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Finding TWO numbers from a range of numbers to match a target value
    By Eero in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2014, 10:08 AM
  2. Replies: 4
    Last Post: 05-08-2014, 09:43 PM
  3. [SOLVED] Finding Consecutive Numbers
    By RebeccaArmstrong in forum Excel General
    Replies: 10
    Last Post: 05-11-2012, 11:52 AM
  4. [SOLVED] Finding consecutive numbers or dates
    By Mattieu in forum Excel General
    Replies: 5
    Last Post: 04-27-2012, 04:42 AM
  5. Replies: 6
    Last Post: 03-23-2012, 06:03 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1