Ordering rows by custom list

theswiftyone

New Member
Joined
Jun 22, 2015
Messages
15
Hi all,

I have outputs that are 169 rows and contain a 'name' (letters & numbers, max 3 characters) and numerical value (float).

I want to order these each rows according to it's name so I can more easily manipulate the data. The problem is the ordering of the name I want does not follow alphabetical order and the order is not intuitive.

I tried 'sort' -> Custom List and it worked.. but it does not accept such a long list of entries (I need 169, it accepted 67). So the first 67 were being sorted and the rest remained in an unworkable order.

Is there a good way around this limitation?

Thanks in advance.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi all,

I have outputs that are 169 rows and contain a 'name' (letters & numbers, max 3 characters) and numerical value (float).

I want to order these each rows according to it's name so I can more easily manipulate the data. The problem is the ordering of the name I want does not follow alphabetical order and the order is not intuitive.

I tried 'sort' -> Custom List and it worked.. but it does not accept such a long list of entries (I need 169, it accepted 67). So the first 67 were being sorted and the rest remained in an unworkable order.

Is there a good way around this limitation?

Thanks in advance.
Hi theswiftyone, this can probably be achieved using VBA (if macros are an option), but we would need to see examples of what these entries look like and also to know what your desired sort criteria is in order to help you put the code together.
 
Upvote 0
Hi Fishboy, appreciate the reply.

The data is actually a list of poker hands with a value assigned to each.

My outputs looks like this (in random order):
Code:
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]AA[/TD]
[TD="width: 64, align: right"]67.78732[/TD]
[/TR]
[TR]
[TD]KK[/TD]
[TD="align: right"]59.42171[/TD]
[/TR]
[TR]
[TD]QQ[/TD]
[TD="align: right"]53.68827[/TD]
[/TR]
[TR]
[TD]JJ[/TD]
[TD="align: right"]49.40803[/TD]
[/TR]
[TR]
[TD]TT[/TD]
[TD="align: right"]45.92639[/TD]
[/TR]
[TR]
[TD]AKs[/TD]
[TD="align: right"]43.87184[/TD]
[/TR]
[TR]
[TD]AQs[/TD]
[TD="align: right"]43.41345[/TD]
[/TR]
[TR]
[TD]AJs[/TD]
[TD="align: right"]42.78924[/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD="align: right"]42.48972[/TD]
[/TR]
[TR]
[TD]ATs[/TD]
[TD="align: right"]41.86729[/TD]
[/TR]
[TR]
[TD]AKo[/TD]
[TD="align: right"]40.52287[/TD]
[/TR]
[TR]
[TD]A9s[/TD]
[TD="align: right"]40.26497[/TD]
[/TR]
[TR]
[TD]A8s[/TD]
[TD="align: right"]39.58414[/TD]
[/TR]
[TR]
[TD="align: right"]88[/TD]
[TD="align: right"]39.58091[/TD]
[/TR]
[TR]
[TD]KQs[/TD]
[TD="align: right"]39.21844[/TD]
[/TR]
[TR]
[TD]A7s[/TD]
[TD="align: right"]39.03906[/TD]
[/TR]
[TR]
[TD]KJs[/TD]
[TD="align: right"]38.4231[/TD]
[/TR]
[TR]
[TD]AQo[/TD]
[TD="align: right"]38.41696[/TD]
[/TR]
[TR]
[TD]A6s[/TD]
[TD="align: right"]38.19951[/TD]
[/TR]
[TR]
[TD]A5s[/TD]
[TD="align: right"]38.11238[/TD]
[/TR]
[TR]
[TD]A4s[/TD]
[TD="align: right"]37.8283[/TD]
[/TR]
[TR]
[TD]KTs[/TD]
[TD="align: right"]37.61787[/TD]
[/TR]
[TR]
[TD]A3s[/TD]
[TD="align: right"]37.42308[/TD]
[/TR]
[TR]
[TD]A2s[/TD]
[TD="align: right"]37.08561[/TD]
[/TR]
[TR]
[TD]AJo[/TD]
[TD="align: right"]36.56615[/TD]
[/TR]
[TR]
[TD="align: right"]77[/TD]
[TD="align: right"]36.46259[/TD]
[/TR]
[TR]
[TD]K9s[/TD]
[TD="align: right"]36.14691[/TD]
[/TR]
[TR]
[TD]QJs[/TD]
[TD="align: right"]35.89633[/TD]
[/TR]
[TR]
[TD]K8s[/TD]
[TD="align: right"]35.31518[/TD]
[/TR]
[TR]
[TD]QTs[/TD]
[TD="align: right"]35.24102[/TD]
[/TR]
[TR]
[TD]ATo[/TD]
[TD="align: right"]35.08856[/TD]
[/TR]
[TR]
[TD]K7s[/TD]
[TD="align: right"]34.84807[/TD]
[/TR]
[TR]
[TD]KQo[/TD]
[TD="align: right"]34.21638[/TD]
[/TR]
[TR]
[TD]K6s[/TD]
[TD="align: right"]34.19236[/TD]
[/TR]
[TR]
[TD]Q9s[/TD]
[TD="align: right"]33.80729[/TD]
[/TR]
[TR]
[TD]JTs[/TD]
[TD="align: right"]33.78757[/TD]
[/TR]
[TR]
[TD]K5s[/TD]
[TD="align: right"]33.55983[/TD]
[/TR]
[TR]
[TD]K4s[/TD]
[TD="align: right"]33.45366[/TD]
[/TR]
[TR]
[TD]K3s[/TD]
[TD="align: right"]33.16267[/TD]
[/TR]
[TR]
[TD="align: right"]66[/TD]
[TD="align: right"]33.14984[/TD]
[/TR]
[TR]
[TD]Q8s[/TD]
[TD="align: right"]33.12403[/TD]
[/TR]
[TR]
[TD]K2s[/TD]
[TD="align: right"]33.0078[/TD]
[/TR]
[TR]
[TD]A9o[/TD]
[TD="align: right"]32.7449[/TD]
[/TR]
[TR]
[TD]J9s[/TD]
[TD="align: right"]32.38913[/TD]
[/TR]
[TR]
[TD]KJo[/TD]
[TD="align: right"]32.34898[/TD]
[/TR]
[TR]
[TD]Q7s[/TD]
[TD="align: right"]31.9967[/TD]
[/TR]
[TR]
[TD]J8s[/TD]
[TD="align: right"]31.85107[/TD]
[/TR]
[TR]
[TD]A8o[/TD]
[TD="align: right"]31.81865[/TD]
[/TR]
[TR]
[TD]T9s[/TD]
[TD="align: right"]31.56072[/TD]
[/TR]
[TR]
[TD]Q6s[/TD]
[TD="align: right"]31.46936[/TD]
[/TR]
[TR]
[TD]A7o[/TD]
[TD="align: right"]31.13013[/TD]
[/TR]
[TR]
[TD]T8s[/TD]
[TD="align: right"]31.02908[/TD]
[/TR]
[TR]
[TD]Q4s[/TD]
[TD="align: right"]31.0117[/TD]
[/TR]
[TR]
[TD]Q5s[/TD]
[TD="align: right"]30.97595[/TD]
[/TR]
[TR]
[TD]KTo[/TD]
[TD="align: right"]30.95923[/TD]
[/TR]
[TR]
[TD]Q2s[/TD]
[TD="align: right"]30.83341[/TD]
[/TR]
[TR]
[TD]Q3s[/TD]
[TD="align: right"]30.78271[/TD]
[/TR]
[TR]
[TD]J7s[/TD]
[TD="align: right"]30.66586[/TD]
[/TR]
[TR]
[TD]98s[/TD]
[TD="align: right"]30.45352[/TD]
[/TR]
[TR]
[TD]QJo[/TD]
[TD="align: right"]30.43856[/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD="align: right"]30.26301[/TD]
[/TR]
[TR]
[TD]A6o[/TD]
[TD="align: right"]29.91934[/TD]
[/TR]
[TR]
[TD]T7s[/TD]
[TD="align: right"]29.85968[/TD]
[/TR]
[TR]
[TD]A5o[/TD]
[TD="align: right"]29.68191[/TD]
[/TR]
[TR]
[TD]J6s[/TD]
[TD="align: right"]29.4661[/TD]
[/TR]
[TR]
[TD]J4s[/TD]
[TD="align: right"]29.42795[/TD]
[/TR]
[TR]
[TD]87s[/TD]
[TD="align: right"]29.40831[/TD]
[/TR]
[TR]
[TD]97s[/TD]
[TD="align: right"]29.33305[/TD]
[/TR]
[TR]
[TD]J3s[/TD]
[TD="align: right"]29.22511[/TD]
[/TR]
[TR]
[TD]J2s[/TD]
[TD="align: right"]29.19265[/TD]
[/TR]
[TR]
[TD]J5s[/TD]
[TD="align: right"]29.17556[/TD]
[/TR]
[TR]
[TD]QTo[/TD]
[TD="align: right"]29.07165[/TD]
[/TR]
[TR]
[TD]A4o[/TD]
[TD="align: right"]28.69065[/TD]
[/TR]
[TR]
[TD]K9o[/TD]
[TD="align: right"]28.65918[/TD]
[/TR]
[TR]
[TD]T6s[/TD]
[TD="align: right"]28.57136[/TD]
[/TR]
[TR]
[TD]JTo[/TD]
[TD="align: right"]28.31401[/TD]
[/TR]
[TR]
[TD]86s[/TD]
[TD="align: right"]28.1697[/TD]
[/TR]
[TR]
[TD]96s[/TD]
[TD="align: right"]28.06505[/TD]
[/TR]
[TR]
[TD]76s[/TD]
[TD="align: right"]28.00453[/TD]
[/TR]
[TR]
[TD]T4s[/TD]
[TD="align: right"]27.93753[/TD]
[/TR]
[TR]
[TD]A3o[/TD]
[TD="align: right"]27.82635[/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD="align: right"]27.74014[/TD]
[/TR]
[TR]
[TD]K8o[/TD]
[TD="align: right"]27.73001[/TD]
[/TR]
[TR]
[TD]T3s[/TD]
[TD="align: right"]27.72557[/TD]
[/TR]
[TR]
[TD]T2s[/TD]
[TD="align: right"]27.70268[/TD]
[/TR]
[TR]
[TD]T5s[/TD]
[TD="align: right"]27.58311[/TD]
[/TR]
[TR]
[TD]85s[/TD]
[TD="align: right"]27.2006[/TD]
[/TR]
[TR]
[TD]K7o[/TD]
[TD="align: right"]27.05626[/TD]
[/TR]
[TR]
[TD]95s[/TD]
[TD="align: right"]27.03925[/TD]
[/TR]
[TR]
[TD]75s[/TD]
[TD="align: right"]27.03543[/TD]
[/TR]
[TR]
[TD]A2o[/TD]
[TD="align: right"]27.00068[/TD]
[/TR]
[TR]
[TD]Q9o[/TD]
[TD="align: right"]26.81135[/TD]
[/TR]
[TR]
[TD]65s[/TD]
[TD="align: right"]26.78984[/TD]
[/TR]
[TR]
[TD]84s[/TD]
[TD="align: right"]26.65698[/TD]
[/TR]
[TR]
[TD]94s[/TD]
[TD="align: right"]26.58973[/TD]
[/TR]
[TR]
[TD]93s[/TD]
[TD="align: right"]26.48094[/TD]
[/TR]
[TR]
[TD]92s[/TD]
[TD="align: right"]26.47487[/TD]
[/TR]
[TR]
[TD]74s[/TD]
[TD="align: right"]26.34609[/TD]
[/TR]
[TR]
[TD]54s[/TD]
[TD="align: right"]26.28145[/TD]
[/TR]
[TR]
[TD]64s[/TD]
[TD="align: right"]26.11023[/TD]
[/TR]
[TR]
[TD]Q8o[/TD]
[TD="align: right"]26.04341[/TD]
[/TR]
[TR]
[TD]K6o[/TD]
[TD="align: right"]26.04145[/TD]
[/TR]
[TR]
[TD]J9o[/TD]
[TD="align: right"]26.03745[/TD]
[/TR]
[TR]
[TD]82s[/TD]
[TD="align: right"]25.83279[/TD]
[/TR]
[TR]
[TD]T9o[/TD]
[TD="align: right"]25.76561[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD="align: right"]25.75741[/TD]
[/TR]
[TR]
[TD]83s[/TD]
[TD="align: right"]25.71682[/TD]
[/TR]
[TR]
[TD]73s[/TD]
[TD="align: right"]25.48728[/TD]
[/TR]
[TR]
[TD]J8o[/TD]
[TD="align: right"]25.40209[/TD]
[/TR]
[TR]
[TD]53s[/TD]
[TD="align: right"]25.37321[/TD]
[/TR]
[TR]
[TD]63s[/TD]
[TD="align: right"]25.13071[/TD]
[/TR]
[TR]
[TD]T8o[/TD]
[TD="align: right"]25.07204[/TD]
[/TR]
[TR]
[TD]43s[/TD]
[TD="align: right"]25.07011[/TD]
[/TR]
[TR]
[TD]K5o[/TD]
[TD="align: right"]25.05197[/TD]
[/TR]
[TR]
[TD]52s[/TD]
[TD="align: right"]24.77417[/TD]
[/TR]
[TR]
[TD]72s[/TD]
[TD="align: right"]24.76884[/TD]
[/TR]
[TR]
[TD]98o[/TD]
[TD="align: right"]24.72329[/TD]
[/TR]
[TR]
[TD]Q7o[/TD]
[TD="align: right"]24.63979[/TD]
[/TR]
[TR]
[TD]42s[/TD]
[TD="align: right"]24.63219[/TD]
[/TR]
[TR]
[TD]62s[/TD]
[TD="align: right"]24.48945[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]24.23656[/TD]
[/TR]
[TR]
[TD]K4o[/TD]
[TD="align: right"]24.20869[/TD]
[/TR]
[TR]
[TD]32s[/TD]
[TD="align: right"]24.03703[/TD]
[/TR]
[TR]
[TD]J7o[/TD]
[TD="align: right"]23.93606[/TD]
[/TR]
[TR]
[TD]87o[/TD]
[TD="align: right"]23.80775[/TD]
[/TR]
[TR]
[TD]Q6o[/TD]
[TD="align: right"]23.76304[/TD]
[/TR]
[TR]
[TD]T7o[/TD]
[TD="align: right"]23.64106[/TD]
[/TR]
[TR]
[TD]K3o[/TD]
[TD="align: right"]23.45165[/TD]
[/TR]
[TR]
[TD]97o[/TD]
[TD="align: right"]23.3254[/TD]
[/TR]
[TR]
[TD]Q5o[/TD]
[TD="align: right"]22.89289[/TD]
[/TR]
[TR]
[TD]K2o[/TD]
[TD="align: right"]22.81641[/TD]
[/TR]
[TR]
[TD]J6o[/TD]
[TD="align: right"]22.31189[/TD]
[/TR]
[TR]
[TD]76o[/TD]
[TD="align: right"]22.22772[/TD]
[/TR]
[TR]
[TD]86o[/TD]
[TD="align: right"]22.17887[/TD]
[/TR]
[TR]
[TD]Q4o[/TD]
[TD="align: right"]22.15735[/TD]
[/TR]
[TR]
[TD]T6o[/TD]
[TD="align: right"]21.93363[/TD]
[/TR]
[TR]
[TD]96o[/TD]
[TD="align: right"]21.65839[/TD]
[/TR]
[TR]
[TD]J5o[/TD]
[TD="align: right"]21.61245[/TD]
[/TR]
[TR]
[TD]Q3o[/TD]
[TD="align: right"]21.45334[/TD]
[/TR]
[TR]
[TD]J4o[/TD]
[TD="align: right"]20.97117[/TD]
[/TR]
[TR]
[TD]Q2o[/TD]
[TD="align: right"]20.93331[/TD]
[/TR]
[TR]
[TD]75o[/TD]
[TD="align: right"]20.79154[/TD]
[/TR]
[TR]
[TD]85o[/TD]
[TD="align: right"]20.72736[/TD]
[/TR]
[TR]
[TD]65o[/TD]
[TD="align: right"]20.58762[/TD]
[/TR]
[TR]
[TD]T5o[/TD]
[TD="align: right"]20.45143[/TD]
[/TR]
[TR]
[TD]J3o[/TD]
[TD="align: right"]20.28557[/TD]
[/TR]
[TR]
[TD]95o[/TD]
[TD="align: right"]20.12042[/TD]
[/TR]
[TR]
[TD]T4o[/TD]
[TD="align: right"]19.93683[/TD]
[/TR]
[TR]
[TD]J2o[/TD]
[TD="align: right"]19.73568[/TD]
[/TR]
[TR]
[TD]74o[/TD]
[TD="align: right"]19.41769[/TD]
[/TR]
[TR]
[TD]54o[/TD]
[TD="align: right"]19.39692[/TD]
[/TR]
[TR]
[TD]84o[/TD]
[TD="align: right"]19.35709[/TD]
[/TR]
[TR]
[TD]64o[/TD]
[TD="align: right"]19.30468[/TD]
[/TR]
[TR]
[TD]T3o[/TD]
[TD="align: right"]19.24504[/TD]
[/TR]
[TR]
[TD]94o[/TD]
[TD="align: right"]18.76527[/TD]
[/TR]
[TR]
[TD]T2o[/TD]
[TD="align: right"]18.70466[/TD]
[/TR]
[TR]
[TD]93o[/TD]
[TD="align: right"]18.19193[/TD]
[/TR]
[TR]
[TD]53o[/TD]
[TD="align: right"]18.09119[/TD]
[/TR]
[TR]
[TD]73o[/TD]
[TD="align: right"]17.99701[/TD]
[/TR]
[TR]
[TD]83o[/TD]
[TD="align: right"]17.89742[/TD]
[/TR]
[TR]
[TD]63o[/TD]
[TD="align: right"]17.85557[/TD]
[/TR]
[TR]
[TD]92o[/TD]
[TD="align: right"]17.66547[/TD]
[/TR]
[TR]
[TD]82o[/TD]
[TD="align: right"]17.53383[/TD]
[/TR]
[TR]
[TD]43o[/TD]
[TD="align: right"]17.4094[/TD]
[/TR]
[TR]
[TD]52o[/TD]
[TD="align: right"]16.95033[/TD]
[/TR]
[TR]
[TD]72o[/TD]
[TD="align: right"]16.78573[/TD]
[/TR]
[TR]
[TD]62o[/TD]
[TD="align: right"]16.66925[/TD]
[/TR]
[TR]
[TD]42o[/TD]
[TD="align: right"]16.42701[/TD]
[/TR]
[TR]
[TD]32o[/TD]
[TD="align: right"]15.63426[/TD]
[/TR]
</tbody>[/TABLE]

I would like them to be listed in order like this:
Code:
AA 
KK 
QQ 
JJ 
TT 
99
88
77
66
55
44
33
22
AKs 
AQs 
AJs 
ATs 
A9s 
A8s 
A7s 
A6s 
A5s 
A4s 
A3s 
A2s 
KQs 
KJs 
KTs 
K9s 
K8s 
K7s 
K6s 
K5s 
K4s 
K3s 
K2s 
QJs 
QTs 
Q9s 
Q8s 
Q7s 
Q6s 
Q5s 
Q4s 
Q3s 
Q2s 
JTs 
J9s 
J8s 
J7s 
J6s 
J5s 
J4s 
J3s 
J2s 
T9s 
T8s 
T7s 
T6s 
T5s 
T4s 
T3s 
T2s 
98s 
97s 
96s 
95s 
94s 
93s 
92s 
87s 
86s 
85s 
84s 
83s 
82s 
76s 
75s 
74s 
73s 
72s 
65s 
64s 
63s 
62s 
54s 
53s 
52s 
43s 
42s 
32s 
AKo 
AQo 
AJo 
ATo 
A9o 
A8o 
A7o 
A6o 
A5o 
A4o 
A3o 
A2o 
KQo 
KJo 
KTo 
K9o 
K8o 
K7o 
K6o 
K5o 
K4o 
K3o 
K2o 
QJo 
QTo 
Q9o 
Q8o 
Q7o 
Q6o 
Q5o 
Q4o 
Q3o 
Q2o 
JTo 
J9o 
J8o 
J7o 
J6o 
J5o 
J4o 
J3o 
J2o 
T9o 
T8o 
T7o 
T6o 
T5o 
T4o 
T3o 
T2o 
98o 
97o 
96o 
95o 
94o 
93o 
92o 
87o 
86o 
85o 
84o 
83o 
82o 
76o 
75o 
74o 
73o 
72o 
65o 
64o 
63o 
62o 
54o 
53o 
52o 
43o 
42o 
32o

I'm aware of macros and have used them, my knowledge of creating and writing them is very limited however.

Any help is much appreciated, thank you.
 
Upvote 0
Hi again the swiftyone, many thanks for sharing this.

Unfortunately this actually generates more questions than it answered. I can now see how you want it sorted but I cannot see the logic behind it. I know you had already said that the sort order wasn't alphabetical, but now I look at it the order seems to be all over the place. Partial alphabetical, partial reverse numeric, mixtures of both.

Is there a reason they are sorted in this order? What are the rules or logic behind them being laid out this way?
 
Upvote 0
There is a reason, I have another program/script that will only read in the data in that order.

It is also a standard convention for poker. The cards are listed in AKQJT98765432 (that is ace, king, queen, jack, ten, 98765432) order.. firstly by paired combinations (AA, KK... 22) then by suited combinations (AKs, AQs ... 32s) and then by offsuit combinations (AKo, AQo ... 32o)

Cheers
 
Upvote 0
Aha! OK, I understand.

The short answer is yes, that is most likely possible using VBA to sort it.

The long answer is wow, that's some hefty criteria right there. According to the little table I just made myself to check there are 325 different possible combinations. I suspect the code used to account for so many variables being fairly epic and unfortunately beyond my current abilities (sorry, I am still learning all this myself).

I'm going to have to open this up the the gurus and hope one of the Excel wizards who have helped me so many times in the past can step in and help you further.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top