Custom Sort Order - Wildcard?
 

Hello,
I'd like to sort a sheet based on an ID column, but have the order
customized. The first part of the ID would be customized, the last part (
after the period) would follow normal sort order.
Customer Order for first part of field:
MB
CL
RX
PV
CU

Each one of them has a period and ascending numbers after them. The list,
after it went through the customized sort would look something like this:
MB.01
MB.02
CL.01
CL.02
CL.03
RX.01
PV.01
CU.01
CU.99
I set up a customized list (Tools/Options/Custom Lists), but there doesn't
seem to be a wild-card feature. I used MB?, MB*, etc, but they don't seem to
work.
Is there a wild-card feature to Customized lists? Or is there another way
to do this? Ultimately, I would call this from Selection.Sort in a Macro.
Many Thanks for your help.
--
Programmer on Budget

Answer: Custom Sort Order - Wildcard?
 

Hello Programmer on Budget,

Yes, you can achieve this custom sort order using a combination of custom lists and formulas. Here are the steps:

1. First, create a custom list for the first part of the ID column. To do this, go to File Options Advanced Edit Custom Lists. In the Custom Lists dialog box, type in the values in the order you want them to appear (MB, CL, RX, PV, CU) and click Add.
2. Next, add a helper column to extract the first part of the ID. Let's assume your ID column is column A and the first ID is in cell A2. In cell B2, enter the formula =LEFT(A2,FIND(".",A2)-1). This will extract the first part of the ID up to the period.
3. Copy the formula down the helper column to apply it to all the IDs.
4. Now, select the entire range of data (including the helper column) and go to Data Sort. In the Sort dialog box, select the helper column (column B) as the first sort key and choose Custom List as the order. Select the custom list you created in step 1.
5. Then, add the ID column (column A) as the second sort key and choose Ascending as the order.
6. Click OK to sort the data. The data should now be sorted in the custom order you specified.
7. If you want to automate this process with a macro, you can record a macro while performing the above steps and then modify the code to suit your needs.

Custom Sort Order - Wildcard?
 

hi
what you want i think is beyound excels built in capabilities. but a helper
column is usually the answer to customs sorts.
add a blank column somewhere(far right usually)
if all you have is the 5 catagories posted, you could use this formula in
the helper column.....
=IF(LEFT(G3,2)="MB",1&G3,IF(LEFT(G3,2)="CL",2&G3,I F(LEFT(G3,2)="RX",3&G3,IF(LEFT(G3,2)="PV",4&G3,IF( LEFT(G3,2)="CU",5&G3,0)))))
copy down.
it basicly puts a number value in front of the catagory in an order you
specify.
sort by the helper column.
if you have more than the catagories posted you could create a small table
off to the side like this...
MB 1
CL 2
RX 3
PV 4
CU 5

then use a vlookup formula like this in the helper column....
=VLOOKUP(LEFT(G4,2),$N$4:$O$8,2)&G4
copy down.
it would produce the same results as the if formula. a shorter formula but
it requires the lookup table.
sort by the helper column.
after the sort, you can delete the help column or hide it for future use.
your call.

regards
FSt1
"Budget Programmer" wrote:

Hello,
I'd like to sort a sheet based on an ID column, but have the order
customized. The first part of the ID would be customized, the last part (
after the period) would follow normal sort order.
Customer Order for first part of field:
MB
CL
RX
PV
CU

Each one of them has a period and ascending numbers after them. The list,
after it went through the customized sort would look something like this:
MB.01
MB.02
CL.01
CL.02
CL.03
RX.01
PV.01
CU.01
CU.99
I set up a customized list (Tools/Options/Custom Lists), but there doesn't
seem to be a wild-card feature. I used MB?, MB*, etc, but they don't seem to
work.
Is there a wild-card feature to Customized lists? Or is there another way
to do this? Ultimately, I would call this from Selection.Sort in a Macro.
Many Thanks for your help.
--
Programmer on Budget