Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Labels:

44 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 07 2022 03:25 PM - edited Sep 07 2022 03:57 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 03:31 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 08:54 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 09:46 AM

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.

Do I also have to paste the formulas/functions from cell A3-A16 somewhere?

I have Microsoft Office Home and Student 2019.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 09:56 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 10:47 AM - edited Sep 08 2022 10:47 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 10:48 AM

@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 11:27 AM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 11:47 AM

@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
)
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 11:54 AM - edited Sep 08 2022 12:17 PM

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:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 12:13 PM - edited Sep 08 2022 12:44 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 01:14 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 01:40 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 01:58 PM - edited Sep 08 2022 02:33 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 02:48 PM

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

"cards" and use ="A123456789TJQKA"

or just open the attachment

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 02:53 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 02:57 PM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 03:02 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 08 2022 03:12 PM

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.*