Customized sort

Hi Everyone,

I'm trying to figure out how I can create a customized sort pattern for a card game macro I'm making.

As an example, in range A2:B6 is one player's hand. column A can have any of the following: " 9", " 10", " Q", " K", " A", " J" (padded with spaces for equal length and readability)
Column B can have any of the following: "Hearts", "Diamonds", "Clubs", "Spades"

I want to sort this range first by column B, then by column A. BUT--I don't want it alphabetic.

For column B, I want it to be sorted using the following: "Hearts" < "Diamonds" < "Clubs" < "Spades"
then column A: " 9" < " 10" < " Q" < " K" < " A" < " J"

What is the best way to go about doing this?

Thanks
Matt

Ok, for this, you have to use a "custom" sort.

In A1:A4 I have

{"Hearts";"Diamonds";"Clubs";"Spades"}

that is, the entries in the order that you want them to.

Now, I go to Tools | Options, Custom Lists

In the "Import list from cells:", select A1:A4 and click "Add"

Now, the entries that I want to sort are in F1:F33, like this:

{"Spades";"Hearts";"Diamonds";"Spades";"Diamonds";"Hearts";"Spades";"Spades ";"Spades";"Clubs";"Spades";"Spades";
"Clubs";"Hearts";"Spades";"Spades";"Spades";"Spades";"Diamonds";"Clubs";"Sp ades";"Spades";"Hearts";"Hearts";"Diamonds";
"Clubs";"Spades";"Clubs";"Hearts";"Hearts";"Hearts";"Hearts";"Spades"}

Select that range, go to Data | Sort, click in "Options" and change the dropdown from "Manual" to "Heards, Diamonds, Clubs, Spades"

Click OK twice, and you're done.

You have to do the same for the numbers too.

Attached is a sample file, I see Juan has laid the roadwork down though. This is utilizing that exact technique actually..

Keep in mind, in this example, you'll get one sort. There's some other randomize code (don't remember where I got it, probably MrExcel) as it's not all needed with the sort. If desired to just randomize (not sort in a specified order) delete the 'customorder..' line from the code.

Excellent, I knew I'd get the answer quick here! Thanks guys!
Matt

Re-opened at Matt's request.

Sorry, Matt. I'm not at home, and I'm on dialup...

Hm. I think that time, I really opened it. Sorry, Matt. My bad, I think!

Not a problem Anne, I haven't really had all that much time to look at this the past few days anyways, so the timing worked out!

firefytr and Juan (and anyone else looking),
One question about this type of thing. If A2:B6 contain a hand, as an example:

9 Clubs
10 Diamonds
K Clubs
9 Spades
K Spades

And I have the following code:
[vba]Sub TestSort()
Dim S1 As Integer
Application.AddCustomList ListArray:=Array("Hearts", "Diamonds", "Clubs", "Spades")
S1 = Application.GetCustomListNum(Array("Hearts", "Diamonds", "Clubs", "Spades"))
Range("A2:B6").Sort Key1:=Range("B2"), Order1:=xlAscending, header:=xlGuess, _
OrderCustom:=S1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub[/vba]
It doesn't work, but if I substitute the sort line above with:
[vba] Range("A2:B6").Sort Key1:=Range("B2"), Order1:=xlAscending, header:=xlGuess, _
OrderCustom:=S1 + 1, MatchCase:=False, Orientation:=xlTopToBottom
[/vba]
It does work. It's almost as though GetCustomListNum has a base of 0, and OrderCustom uses the same list with a base of 1. That's really my only guess, aside from the GetCustomListNum doesn't count the default "NEW LIST" as an entry, but OrderCustom does. Will this always be the case, where +1 will work?
Thanks again!
Matt