Sorting Naturally

  • Thread starter Thread starter wutzke
  • Start date Start date
W

wutzke

I have a list of shirts with size from Small (SR) to 5X (5XLR). When
sorted they organize themselves like this

CHH-2XLR
CHH-2XLT
CHH-3XLR
CHH-3XLT
CHH-4XLR
CHH-4XLT
CHH-5XLR
CHH-LR
CHH-LT
CHH-MR
CHH-SR
CHH-XLR
CHH-XLT


Is there a way to sort them into the real order? Small, Medium, Large,
Large Tall, XLarge, XL Tall...
 
Assuming the fist value is in A1:
Method 1) in B1 enter =MID(A1,5,255);copy down the column and sort column B
Method 2) select all the data, use Data|Text to Columns; specify Delimited
and set hyphen (-) as the delimiter. No you can again sort on B column
best wishes
 
Assuming the fist value is in A1:
Method 1) in B1 enter =MID(A1,5,255);copy down the column and sort column B
Method 2) select all the data, use Data|Text to Columns; specify Delimited
and set hyphen (-) as the delimiter. No you can again sort on B column
best wishes
Click to expand...


That's two great ideas, thanks. But that seems to keep them in the
same order.
These are a list of sizes. SR is Small, which is the smallest size. MR
is medium, the next size, then LR (Large) and then LT (large tall).
Once sorted SR would be 1st and the biggest size (5XLR) would be last.

I think this may be logically impossible.
 
This should do it seamlessly if you have a blank column B

Sub sortbyclothingsize()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "a").End(xlUp).Row
Set MRNG = Range("a2:a" & lr)
'make helper column
For Each C In MRNG
Select Case Mid(C, InStr(C, "-") + 1, 1)
Case "S": MNUM = 1
Case "M": MNUM = 2
Case "L": MNUM = 3
Case "X": MNUM = 4

Case "1": MNUM = 6
Case "2": MNUM = 7
Case "3": MNUM = 8
Case "4": MNUM = 9
Case "5": MNUM = 10

Case Else
End Select
C.Offset(, 1) = MNUM
Next C
'sort
MRNG.Resize(, 2).Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Columns("B").Delete
Application.ScreenUpdating = True
End Sub
 
You need to set up a table
Sr 1
mr 2
etc
Then use VLOOKUP in column C to get a number for each B value; convert
formulas to values with Paste Special and sort ton this data
best wishes

Assuming the fist value is in A1:
Method 1) in B1 enter =MID(A1,5,255);copy down the column and sort column
B
Method 2) select all the data, use Data|Text to Columns; specify Delimited
and set hyphen (-) as the delimiter. No you can again sort on B column
best wishes
Click to expand...


That's two great ideas, thanks. But that seems to keep them in the
same order.
These are a list of sizes. SR is Small, which is the smallest size. MR
is medium, the next size, then LR (Large) and then LT (large tall).
Once sorted SR would be 1st and the biggest size (5XLR) would be last.

I think this may be logically impossible.
 
Another way is to use a Custom List.

You'll still have to parse your data into separate columns (unless CHH- is
always part of the sizes).

But then you can sort that list manually in the order you want.

Then select that range.
(in xl2003 menus)
Tools|options|custom list
Click the import button
(since you selected the range in the correct order, it should be filled in)

Or you could type them in manually.

Then when you sort, you can use:
Select the range (all the columns in the range)
(still xl2003 menus)
Data|Sort|Click the options button
and change the first key sort order to your custom list.

You can also use this custom list to fill a range--just like days and numbers!
 
To extend Dave's idea to 2007:

A. Reorder the list manually.
B. Create the custom list
1. Select the sorted list
2. Choose Office Button, Excel Options, Popular, Edit Custom Lists, and
click Import
3. Click OK twice.
C. Use the custom list to control sort order
1. Select the data you want sorted and choose Data, Sort
2. If necessary change the Sort by to the column of the data that is
controlling the sort,
3. In the Sort On box leave Values on
4. In the Order box pick Custom List
5. Pick the list you created earlier.

There are quite a few changes to the Sort command in 2007, one of them is
that you can apply the Custom List to any level of your sort. In 2003 this
option is only available for the first sort key.
 
Back
Top