Sep 07 2022 12:48 PM
Dear community,
With your help I was able to solve problems in the past. I hope you can help me again.
I need a formula that enables me to filter on 3(or more) consecutive cards. The 4th card from column B in combination with 3 cards from column A.
In the enclosed example. An 8 and J gives 3 consecutive cards with T95(JT95 and T985).
With A43 a 2 gives 4 consecutive cards(A432). A 5 gives 3 consecutive cards(A435).
With 765, both an 8 and 4 give 4 consecutive cards(8765 and 7654).
Potential problems
1. The numbers 2-9 are logically consecutive for Excel. But I am concerned if 9TJ can be seen as such as well. Since they are letters and not numbers.
2. Same as in Rummikub. A234 is consecutive. QKA2 is NOT. The A is the final card for both ways.
In the enclosed file I showed for T95 how I would like to see it. I couldn't add 3 other cards. Since an 8 would not make 3 consecutive cards with A43 for example.
If something is unclear. Please don't hesitate and ask me to clarify.
Thank you for your help and effort.
Sep 08 2022 03:16 PM
Sep 08 2022 04:08 PM
=OR(
ABS((SEARCH(LEFT(A150,1),cards)-SEARCH(MID(A150,4,1),cards))*(SEARCH(LEFT(A150,1),cards)-SEARCH(LEFT(B150,1),cards))-0.5)=1.5,
ABS((SEARCH(LEFT(A150,1),cards)-SEARCH(MID(A150,7,1),cards))*(SEARCH(LEFT(A150,1),cards)-SEARCH(LEFT(B150,1),cards))-0.5)=1.5,
ABS((SEARCH(MID(A150,4,1),cards)-SEARCH(MID(A150,7,1),cards))*(SEARCH(MID(A150,4,1),cards)-SEARCH(LEFT(B150,1),cards))-0.5)=1.5
)
You would only have to delete the first line of your formula so that the fourth card is always determined. Besides, if there are 4 in the row with the 4 card, then there are also three.
The formula works, I think the name "cards" was not assigned to the file in your post.
The calculation you used impresses me. I was not aware of the connection. Is that also possible for 4 digits?
Sep 08 2022 04:30 PM
Sep 08 2022 05:37 PM
Sep 09 2022 05:27 AM
Sep 09 2022 05:40 AM
Sep 09 2022 05:56 AM
@Kalthof OK I'm sorry I found a couple of errors in the previous solutions.
a) my named constant "cards" was wrong because it has the number 1 in it. It should have been A23... instead of A123...
b) although I included A at the beginning and end the one on the end was never and would never be used and hence no solution using A high would register.
In the attached I corrected for the above by creating "cardsLO" and "cardsHI" and checking BOTH. I also output "3 in Flop" if that is the case and renamed the column to be 3 to a straight and added another column for 4 to a straight
Sep 09 2022 05:59 AM
Sep 09 2022 07:44 AM - edited Sep 09 2022 08:29 AM
You are fast! I love that you isolated 4th to straight. That enables me to filter more precise.
There are 2 small things. Can you remove the '3 on flop' filter option and put them under the FALSE flag. For the turn strategy this is not relevant. Flop strategies already factor in the connectivity.
Is it maybe possible to combine the 3&4 to Straight columns? So it gives me the options: TRUE, FALSE and 4 connected. 4 connected = 4th to Straight. So basically 3 on flop replaced by 4th to straight.
Some blanks (these are averages in my worksheet) are flagged TRUE. Can you flag these as FALSE?
See Gyazo: https://gyazo.com/7346e5e68427787104329ce32414a25a
Sep 09 2022 09:04 AM
@Kalthof I combined the columns as requested and added a check for a blank. I return blank if either input cell is blank but you can change that. Here is the formula:
= if((A2="")+(B2=""),"",
if(("Check for 4card straight"="")+
(VARPA(SEARCH(LEFT(A2,1),cardsLO),SEARCH(MID(A2,4,1),cardsLO),SEARCH(MID(A2,7,1),cardsLO),SEARCH(LEFT(B2,1),cardsLO))=1.25)+
(VARPA(SEARCH(LEFT(A2,1),cardsHI),SEARCH(MID(A2,4,1),cardsHI),SEARCH(MID(A2,7,1),cardsHI),SEARCH(LEFT(B2,1),cardsHI))=1.25),
"4 connected",
IF(("Check for 3 connected in flop"="")+
(VARA(SEARCH(LEFT(A2,1),cardsLO),SEARCH(MID(A2,4,1),cardsLO),SEARCH(MID(A2,7,1),cardsLO))=1)+
(VARA(SEARCH(LEFT(A2,1),cardsHI),SEARCH(MID(A2,4,1),cardsHI),SEARCH(MID(A2,7,1),cardsHI))=1),
False,
OR(
"Check each combo of 3 assuming ace low"="",
VARA(SEARCH(LEFT(A2,1),cardsLO),SEARCH(MID(A2,4,1),cardsLO),SEARCH(LEFT(B2,1),cardsLO))=1,
VARA(SEARCH(LEFT(A2,1),cardsLO),SEARCH(MID(A2,7,1),cardsLO),SEARCH(LEFT(B2,1),cardsLO))=1,
VARA(SEARCH(MID(A2,4,1),cardsLO),SEARCH(MID(A2,7,1),cardsLO),SEARCH(LEFT(B2,1),cardsLO))=1,
"Check each combo of 3 assuming ace high"="",
VARA(SEARCH(LEFT(A2,1),cardsHI),SEARCH(MID(A2,4,1),cardsHI),SEARCH(LEFT(B2,1),cardsHI))=1,
VARA(SEARCH(LEFT(A2,1),cardsHI),SEARCH(MID(A2,7,1),cardsHI),SEARCH(LEFT(B2,1),cardsHI))=1,
VARA(SEARCH(MID(A2,4,1),cardsHI),SEARCH(MID(A2,7,1),cardsHI),SEARCH(LEFT(B2,1),cardsHI))=1
)
)
))
As you can see I also added "comments" to help you see/understand what each part of the formula does. These comments don't effect the calculations as they evaluate to false/0 and are part of an OR or sum.
Sep 09 2022 09:22 AM - edited Sep 09 2022 09:23 AM
Thank you so much. You are the best!
Just to be sure. I don't have to add column E with this filter anymore right? Column D with it's filter is sufficient now?
=OR(VARPA(SEARCH(LEFT(A4,1),cardsLO),SEARCH(MID(A4,4,1),cardsLO),SEARCH(MID(A4,7,1),cardsLO),SEARCH(LEFT(B4,1),cardsLO))=1.25,VARPA(SEARCH(LEFT(A4,1),cardsHI),SEARCH(MID(A4,4,1),cardsHI),SEARCH(MID(A4,7,1),cardsHI),SEARCH(LEFT(B4,1),cardsHI))=1.25)
Since you are able to filter out 4 connected cards. Are you able to filter out ''gutshots'' as well? What I mean by that is that exactly 1 card makes a straight.
So for example 567+9, the 8 makes the straight here.
Or 568+9, a 7 makes the straight.
Or 579+6/8, a 6 or 8 make a straight
Or 569+8, 7 makes the straight
Thank you again. You have no idea how much I appreciate you!
Sep 09 2022 10:08 AM
SolutionSep 09 2022 10:34 AM - edited Sep 09 2022 11:31 AM
No worries. You deserve all the likes I can give you haha. And indeed this is always an inside straight.
Sep 09 2022 04:22 PM
Sep 12 2022 02:29 PM
Sep 12 2022 04:34 PM
Sep 12 2022 04:38 PM
Sep 13 2022 06:23 AM - edited Sep 13 2022 06:29 AM
My goal is to make rename the FALSE and TRUE names in this list. I am not the only one to use this worksheet so I need to clarify things for them. In the formula you included "4 connected" for example. I really like that you included this. And I know that I can rename this. But TRUE/FALSE is not in-between quotation marks. So I don't know how the rename that. ''3 connected'' is easier to understand than ''TRUE''.
https://gyazo.com/4a92e07ce7c787ddce6c6778bf75d5f1
Can I just add the paired column to formula in the ''Straight'' column as well?
=IFERROR(IF(FIND(LEFT(B4,1),A4),"Exist","Not Exist"),"Not Exist")
I tried to add it between these lines of your formula. But it didn't work.
= IF((A4="")+(B4=""),"",
IF(("Check for 4-card straight"="")+
My goal is to have everything under 1 column. It looks cleaner and people can just choose in the list what they want to filter for.
So that the list would become like this:
- Paired
- 4 Connected
- 3 Connected
- Gutshot
- Nothing
Sep 13 2022 09:30 AM
@Kalthof Updated accordingly. I left the "paired" column in just for you to compare if you like but it isn't needed now. Are you sure you want "FALSE" shown for the rest? maybe "n/c" instead? What about pairs or 3 connected on flop? Should those have anything like "n/c - pair" and "n/c - 3 connected"?