Sort my mixed cards (values) as my formulas like

**muraterguden** · 05-26-2019, 04:56 PM

Hi,
I am working on some poker game variations. So far, I have managed to create some kind of data base where my staff enters the qualified combinations for the on going promotion and I am able to sort them from the best hand to the worst.
However, my staff keep entering some combinations in descending order and that order does not work with my formulas, therefore possibly winning hands are not recognized.

I think I have reached my Excel knowledge limit and I could not find any way to sort unorderly entered card combinations as in the picture below.

Any help is highly appreciated. Thanks in advance.

Screenshot 2019-05-26 19.17.49.png

**GeoffW283** · 05-26-2019, 07:19 PM

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

**muraterguden** · 05-26-2019, 07:26 PM

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

**GeoffW283** · 05-26-2019, 10:38 PM

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:

=INDEX($E$1:$E$52, MATCH(G4, $B$1:$B$52, 0))

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:

=INDEX($B$1:$B$52, MATCH(SMALL($M4:$Q4, COLUMNS(S:$S)), $E$1:$E$52, 0))

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

**muraterguden** · 05-27-2019, 12:20 PM

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

**GeoffW283** · 05-27-2019, 02:33 PM

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

**Bo_Ry** · 05-27-2019, 02:39 PM

Please try at K4

Formula:

=IFERROR(IF(OR(FREQUENCY(INT((MATCH($E4:$I4,$A$1:$A$52,)-1)/13),{0,1,2})>2),

INDEX($A$1:$A$52,MOD(AGGREGATE(15,6,IF(COUNT(MATCH({2,3,14},MOD(MATCH($E4:$I4,$A$1:$A$52,)-2,13)+2,))=3,MOD(MATCH($E4:$I4,$A$1:$A$52,)-1,13)+1,MOD(MATCH($E4:$I4,$A$1:$A$52,)-2,13)+2)/(MAX(IF(FREQUENCY(INT((MATCH($E4:$I4,$A$1:$A$52,)-1)/13),{0;1;2})>2,{0;1;2;3}))=INT((MATCH($E4:$I4,$A$1:$A$52,)-1)/13)),COLUMNS($K4:K4))-1,13)+1+MODE(INT((MATCH($E4:$I4,$A$1:$A$52,)-1)/13))*13),

INDEX($A$1:$A$52,AGGREGATE(15,6,MATCH($E4:$I4,$A$1:$A$52,)/(MOD(MATCH($E4:$I4,$A$1:$A$52,)-2,13)+2=MODE(MOD(MATCH($E4:$I4,$A$1:$A$52,)-2,13)+2)),COLUMNS($K4:K4)))),

INDEX($A$1:$A$52,MOD(AGGREGATE(15,6,IF(COUNT(MATCH({2,14},MOD(MATCH($E4:$I4,$A$1:$A$52,)-2,13)+2/(1-COUNTIF($J4:J4,$E4:$I4)),))=2,MOD(MATCH($E4:$I4,$A$1:$A$52,)-1,13)+1,MOD(MATCH($E4:$I4,$A$1:$A$52,)-2,13)+2)/(1-COUNTIF($J4:J4,$E4:$I4))*100+MATCH($E4:$I4,$A$1:$A$52,),1),100)))

Press Ctrl+Shift+Enter and drag across

I did't test with other case.
Please don't ask me to explain, probably take a week.

**muraterguden** · 05-27-2019, 04:22 PM

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?

**Bo_Ry** · 05-28-2019, 06:25 AM

Please provide sample and expected answer.

I explain some

=OR(FREQUENCY(INT((MATCH($E4:$I4,$A$1:$A$52,)-1)/13),{0,1,2})>2)
Check if there are 3 or more flush

=IF(COUNT(MATCH({2,3,14},MOD(MATCH($E4:$I4,$A$1:$A$52,)-2,13)+2,))=3,MOD(MATCH($E4:$I4,$A$1:$A$52,)-1,13)+1,MOD(MATCH($E4:$I4,$A$1:$A$52,)-2,13)+2)
J=11, Q=12 , K=13, A=14 check if A should be 14 or 1 by check if 2,3,14(A) are in the the 5 cards.

Sort my mixed cards (values) as my formulas like

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Sort my mixed cards (values) as my formulas like

Re: Sort my mixed cards (values) as my formulas like

Re: Sort my mixed cards (values) as my formulas like

Re: Sort my mixed cards (values) as my formulas like

Re: Sort my mixed cards (values) as my formulas like

Re: Sort my mixed cards (values) as my formulas like

Re: Sort my mixed cards (values) as my formulas like

Re: Sort my mixed cards (values) as my formulas like

Re: Sort my mixed cards (values) as my formulas like

Thread Information

Users Browsing this Thread

Similar Threads

wild cards in formulas and functions

wild cards in formulas and functions

[SOLVED] wild cards in formulas and functions

[SOLVED] wild cards in formulas and functions

[SOLVED] wild cards in formulas and functions

wild cards in formulas and functions

wild cards in formulas and functions

wild cards in formulas and functions

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions