Forum Discussion
Formula for playing cards
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...
- correct the prior Column E should have been deleted
so basically 4 cards to an inside straight possibilities. I will see if I can come up with something but it will cost you. jk. but a few likes wouldn't hurt. lol.
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:
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.
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.
- 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 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.
- please take a look at my last post and see if that is right. I think it is working.
