Forum Discussion
Solved
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 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.
- 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.
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.
- 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.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?
