Sort my mixed cards (values) as my formulas like

By default, Sort works on columns. I think you want to sort rows. To do that on the ribbon: Data > Sort > Options > "Sort left to right"

You will probably also need to define a custom sort list. To do this, select the "Other" dropdown in the sort dialog box. Like this:

customSort.png

Now select "Custom list" then "New list". Enter the List entries in the order you want then click "Add"

I can't tell from the picture you posted whether this approach will work or not and I can't give you more detailed instructions without seeing the workbook itself.

If you need more than the above outline then please attach your workbook.

To attach a workbook (first remove any sensitive data), click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.

I have of course tried that but its way too far from what i need. I will try to attach a sample file.

Here is an attempt by formula.

poker.png

In a few cases it does not reproduce the expected results exactly, but maybe some or all of the differences are acceptable. I'm no poker player so I will leave it to you to decide if there is any value to this approach.
I added column-E to value the cards from lowest to highest. It goes from 1 for the 2 of spades to 52 for the Ace of hearts.
M4:Q14 is a helper range that takes the entered cards in G4:K14 and translates to the col-E values. In M4 copied across and down:

Formula:

Please Login or Register  to view this content.

My solution is in S4:W14 and arranges the M4:Q4 row in ascending order and then translates back to the card names. The same thing for rows 5 thru 14. In S4 copied across and down to W14:

Formula:

Please Login or Register  to view this content.

There's not much flexibility in this approach to "tune" it as far as I can see currently, so it's either acceptable as is or it is of no value to you.

I've attached your workbook with the above changes included.

Let me know what you think

"There's not much flexibility in this approach to "tune" it as far as I can see currently, so it's either acceptable as is or it is of no value to you."
Well, it has a value to me because you actually spent time and tried to help. So, thank you.
I see what you tried to do. It normally makes sense but the cards must be displayed as "HOW IT IS SUPPOSE TO BE" table for casino people to understand and my formulas to work.
I have achieved similar results with Sort>Custom list option but unfortunately, it wasn't giving me what I needed.
Well, I think I will have to play with your formula and see if I can use some nested IF functions to overcome the problems...

Lets see if anyone else on the forum can make progress - I'm out of ideas I'm afraid.

OK, I won't ask... And thank you...
As far as I see, it works for what my file needs. Now I need to embed these formulas to my actual file. The formula you created is way too complex for me but let it be my challenge.
The formula is one step away from perfection tho. If there is a small pair, consisting of two cards they are sent back to the last position if the rest of the cards are bigger. Any pair(s) of two( and for some poker games Ace+King too) should be displayed on the left. Would that be achieved easily, since the hard work is already done?