Re: Sorting alphanumeric

K,

There is some fairly detailed instructions on getting started with macros/vba
by F. David McRitchie at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

See if this makes sense first...
Only use the code from my post, that portion between the horizontal
dashed lines. It goes in a general/standard code module which is
accessed from the keyboard with Alt + F11. There should be a large
window on the right side in which to paste the code. If not, then from
the menu bar go to Insert | Module.
Once the code is pasted, you can run it from the spread*** by using:
Tools | Macro | Macros and selecting the name of sub/code and clicking
the Run button.
Make sure you have selected the column of data you want to sort.
Try it on a some test data first.

Regards,
Jim Cone
San Francisco, USA


"KWBock" <KWBock@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9B9311E1-9697-410B-AFEC-C486553693EA@xxxxxxxxxxxxxxxx
Jim,

Thanks for the reference. It sounds like it would work. However, I'm very
inexperienced when it comes to macros/VBA. I tried entering the code that you
had in the referenced post, but I couldn't get it to run. Do you have
simplified instructions on where to enter the code and how to get it to run?
I apologize for my inexperience. But I appreciate you taking the time to
help. Thanks.
K. Bock

"Jim Cone" wrote:
> K,
> Maybe this recent post of mine will help...
>
> http://makeashorterlink.com/?R2B662F4B
>
> The code in the post creates additional data columns that can be
> used to sort. These are:
> "Length", "Prefix", "Padded #", "Suffix", "Combined", "Reversed"
>
> In the "Padded #" column, the program pulls the last (right most)
> group of contiguous numbers from each cell.
> It pads the group with enough leading zeros so as to equal the
> length of the longest group in the entire selection.
> This allows the selection to be sorted in strict numerical order.
> Jim Cone
> San Francisco, USA
>
>
>
>
> "KWBock" <KWBock@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:0CB9A968-9755-4AEC-9069-29DDBD70C554@xxxxxxxxxxxxxxxx
> I've been searching through several old topics on alphanumeric sorts and have
> not seen any problem that resembles mine. So, any help is appreciated.
>
> I have a work*** with several columns (~10). I am currently sorting this
> work*** based on three columns (e.g. A then B then C). Column A is a
> text-formatted family name for a group of data within the work***. Column B
> is a family number. This further breaks down the families into "subfamilies,"
> if you will, based on the family numbers. The final column is each item's
> name, which are alphanumeric. Here is a very crude representation of this
> that hopefully is easier to understand:
>
> COLUMN A COLUMN B COLUMN C
> AMF 1 item1
> FAM 1 item10
> FAM 1 item2
> FAM 2 item45
> FAM 3 item67
> FAM 3 item7
>
> The problem is the sorting that Excel does in Column C. All of the cells are
> formatted as text because there is text present. Because of this, if I have
> say ten items under one family number, Excel will sort 1, 10, 2, 3, 4, and so
> on. I want item10 to be last in this instance. I know one easy fix would be
> to make item1 item01. But that would ultimately change the name of each item
> (the actual names are more elaborate than "item1" and are published without
> the added zero, so it wouldn't be kosher to add a number) and take too much
> time.
> Is there any other way to get column C to sort the way I want?
> Thanks in advance.
> K
.

Quantcast