Formula for playing cards

[Kalthof]

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.

[#2]

@Kalthof 

Hi, attached my first attempt. I hope it fits already to some extent. When analyzing the data, I had to use an auxiliary column. Also, I'm not sure if I got the ace thing right. I set ace to 0, which in no case leads to a row of three or four.

Have a look at the result and give feedback if it fits or what needs to be changed.

[#3]

Thank you for your first attempt.

I should have clarified better. I enclosed an example where I put in 3 different 3 card examples. In my complete document I have almost 200 different combinations. That gives almost 10.000 rows. So I made a simplified file.

Also I put in the paired column in column C. You don't have to use the method I used there. But I thought maybe leave it in there so it might give you some ideas.

Your method seems to work but I see an issue. It must the be the 4th card that changes the sequence. So if the K on 765 doesn't give a 4th consecutive card. It doesn't count. An 8 or 4 would on the contrary. I understand the misunderstanding because it was already 3 in a row with the first 3 cards. I should have clarified that better as well.

After I filter on true or false I get an #NAME? error by the way. After that I can't undo my last action. All the cells have #NAME? now and I can't filter on true/false anymore. I have to reopen the document to have that option again.

Thank you for your effort so far.

[#4]

@Kalthof 

Hello Jeroen, the bad news is that with a #NAME? error, your Excel version does not recognise one of my formulas or functions. Before I can look further into the solution, I need to know which function is not working for you.

Please open the attached sheet and perform a new calculation in the worksheet so that the formulas are recalculated. Which formula(s) do not work?

[#5]

I have to paste the formula in A1 from Error Test in which cell of the worksheet Playing cards?
Do I also have to paste the formulas/functions from cell A3-A16 somewhere?

I have Microsoft Office Home and Student 2019.

[#6]

@Kalthof 

No, you do not have to copy anything. Please just run a new calculation of the cells (F9) in Error Test and tell me in which cells (A3:A16) the #NAME? error appears.

[#7]

I ran the calculation but no #NAME? appears.

Here is a Gyazo to show you that. You can also see what happens if I filter on true in the other worksheet.

https://gyazo.com/ad72f19e4622866fe34c5ccfe44d5c84

[#8]

@dscheikey I don't think any of the new formulas will work for them as they are using 2019.  Maybe this will work?

=OR(
AND(ABS(SEARCH(LEFT(A3,1),cards)-SEARCH(MID(A3,4,1),cards))=1,ABS(SEARCH(MID(A3,7,1),cards)-SEARCH(MID(A3,4,1),cards))=1,LEFT(A3,1)<>MID(A3,7,1)),
AND(ABS(SEARCH(MID(A3,7,1),cards)-SEARCH(MID(A3,4,1),cards))=1,ABS(SEARCH(MID(A3,7,1),cards)-SEARCH(LEFT(B3,1),cards))=1,MID(A3,4,1)<>LEFT(B3,1))
)

Since there are only 4 cards then either cards 1,2,3 or 2,3,4 need to have a sequence to work ... oh shoot there is another scenario ... the "inside straight draw".  I'll think how to add another condition for that ... but for now the attached will look for 3 cards in a sequence. (BTW "cards" is a defined name for the text string "A123456789TJQKA".

Let me know if we are at least going in the right direction.

[#9]

based your prior reply I saw that a sequence of 3 could pre-exist and should be detected
also an out of sequence consecutive (inside straight) should also be detected right? (i.e. 3,1,2)
and in 'inside 4 card straight' should be detected right? (i.e. 3,4,1,2)
but not accept if a middle card is not part of the sequences (i.e. 3,4,8,5) should be FALSE
and based on your formula for "Paired" is it true no initial 3 cards will have a pair?

[#10]

@Kalthof  Ok I re-read the posts again and now back to thinking as long as ANY 3 cards make a sequence of 3 in a row you want it true so try this formula:

=OR(
ABS((SEARCH(LEFT(A46,1),cards)-SEARCH(MID(A46,4,1),cards))*(SEARCH(LEFT(A46,1),cards)-SEARCH(MID(A46,7,1),cards))-0.5)=1.5,
ABS((SEARCH(LEFT(A46,1),cards)-SEARCH(MID(A46,4,1),cards))*(SEARCH(LEFT(A46,1),cards)-SEARCH(LEFT(B46,1),cards))-0.5)=1.5,
ABS((SEARCH(LEFT(A46,1),cards)-SEARCH(MID(A46,7,1),cards))*(SEARCH(LEFT(A46,1),cards)-SEARCH(LEFT(B46,1),cards))-0.5)=1.5,
ABS((SEARCH(MID(A46,4,1),cards)-SEARCH(MID(A46,7,1),cards))*(SEARCH(MID(A46,4,1),cards)-SEARCH(LEFT(B46,1),cards))-0.5)=1.5
)

[#11]

@mtarler 

Very very good approach!

I have also tinkered with your formula.

=AND(
    AND(ABS(SEARCH(LEFT(A3,1),cards)-SEARCH(MID(A3,4,1),cards))=1,ABS(SEARCH(MID(A3,4,1),cards)-SEARCH(MID(A3,7,1),cards))=1,LEFT(A3,1)<>MID(A3,7,1))=FALSE,
        OR(
             AND(ABS(SEARCH(LEFT(A3,1),cards)-SEARCH(MID(A3,4,1),cards))=1,ABS(SEARCH(MID(A3,4,1),cards)-SEARCH(LEFT(B3,1),cards))=1,LEFT(A3,1)<>LEFT(B3,1)),
             AND(ABS(SEARCH(LEFT(A3,1),cards)-SEARCH(MID(A3,7,1),cards))=1,ABS(SEARCH(MID(A3,7,1),cards)-SEARCH(LEFT(B3,1),cards))=1,LEFT(A3,1)<>LEFT(B3,1)),
             AND(ABS(SEARCH(MID(A3,4,1),cards)-SEARCH(MID(A3,7,1),cards))=1,ABS(SEARCH(MID(A3,7,1),cards)-SEARCH(LEFT(B3,1),cards))=1,MID(A3,4,1)<>LEFT(B3,1))
))

It must be the 1,2,4 or 1,3,4 or 2,3,4 neighbours. But 1,2,3 alone is not. That is how I understood it.

This is the result:

[#12]

You are thinking along the good lines. To give a bit of background information. The data is used for optimal game theory strategies in poker. A strategy is based on the first 3 cards, the flop. The 4th card, the turn, either significantly changes that strategy or not. That is why it has to be the 4th card in combination with the first 3 cards.

2 examples of that strategy changing significantly is the board pairing (AKQQ eg) or a straight draw turning into a potential straight. As you smartly recognized. The most significant change for straights will be when the 4th card leads to 3 or more cards in a row. So it doesn't matter if that 4th card is on the left, middle or right. They all must be TRUE for the formula.

I understand you guys are making progress. Let me know when I can try it out. I very much appreciate you guys working together and helping me with this. Thank you!

Ps. I am not 100% sure I have answered your questions with this message. Let me know if I have to clarify something.

About pre-existing(flop) 3 card in a row sequences like 765. Here it also has to be in combination with the 4th card. So when the 4th card(turn) is a K. The strategy doesn't significantly change when the board becomes 765K. On the contrary, if an 8 would come on the board. The strategy changes signficantly.

[#13]

This looks good by the way. Just to be sure. The J would also be TRUE for T95. And a 5 would also be TRUE for A43. But maybe I just can't see it from that picture.

[#14]

please take a look at my last post and see if that is right. I think it is working.

[#15]

I get the #NAME? error. See new Gyazo.
https://gyazo.com/330c89121f5ff28c4ae7b374ea79db64

Can you maybe post the worksheet including the formula? Or will that not make a difference?

I tried adding the filter on my phone as well but same error.

[#16]

did you define "cards" as a the name? Click on Formulas then Name Manager then New and add:
"cards" and use ="A123456789TJQKA"
or just open the attachment

[#17]

@Kalthof 

I have now added a relatively wild part to my formula that checks for 4 in a row. My formula had not done that before. I'm not sure if Excel 2019 can do the formula. Theoretically, yes.

SUMPRODUCT(--(AGGREGATE(15,0,CHOOSE(ROW($1:$4),SEARCH(LEFT(A4,1),cards),SEARCH(MID(A4,4,1),cards),SEARCH(MID(A4,7,1),cards),SEARCH(LEFT(B4,1),cards)),ROW($1:$4))= ROW(OFFSET(A$1,MIN(CHOOSE(ROW($1:$4),SEARCH(LEFT(A4,1),cards),SEARCH(MID(A4,4,1),cards),SEARCH(MID(A4,7,1),cards),SEARCH(LEFT(B4,1),cards)))-1,1,4))))

The result would look like this:

Enclosed is the document for you to try out.

[#18]

@dscheikey  thx for the compliment and glad you like the approach.

BTW i thought I would give some explanation to my updated formula (posted above):

basically for any 4 cards there are 4 combinations of 3 cards.  If you label them as A,B,C,D then you can group them as:

A,B,C;  A,B,D;   A,C,D;   or B,C,D

to know if any group of 3 cards is a sequence then if you take the difference between one and each of the other 2 and multiply them you must get either -1 or +2 (e.g. 4,5,3 would be +1 * -1 = -1 or 5,4,3 would be -1 * -2 = +2)

So I have 4 lines for those 4 groupings and after I multiply them together I -0.5 so I can then take absolute value and compare with 1.5 (i.e. -1-0.5 = -1.5 or +2-0.5 = 1.5).

Hope that makes some sense :)

[#19]

So 2 follow up:
a) does 4 in a row matter? I think it would but the OP didn't mention that
b) I'm still confused if the case where the first 3 are already a sequence should or should not be flagged as true. In my formula above the 1st of the nearly identical lines checks for that so could be pulled out of the OR and made part of an IF or something else.

[#20]

@mtarler 

In the third reply@Kalthof wrote: It must the be the 4th card that changes the sequence. So if the K on 765 doesn't give a 4th consecutive card. It doesn't count. An 8 or 4 would on the contrary. I understand the misunderstanding because it was already 3 in a row with the first 3 cards. I should have clarified that better as well.