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,IF(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
|
Similar Threads
