Sorting alphanumeric

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 worksheet with several columns (~10). I am currently sorting this
worksheet 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 worksheet. 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. Bock

Hello KWBock,

Neither Excel nor VBA has a sorting method to do what you want. A custom sorting algorithm would have to be written in VBA to accomplish the task. Depending on how elaborate the naming convention is, the time needed to construct such a sorting algorithm may out way the time it takes to add in the zeroes. If you have a large amount of data, then spending time on the algorithm would be worth the effort.

Sincerely,
Leith Ross

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@discussions.microsoft.com> wrote in message
news:0CB9A968-9755-4AEC-9069-29DDBD70C554@microsoft.com...
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 worksheet with several columns (~10). I am currently sorting this
worksheet 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 worksheet. 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. Bock

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@discussions.microsoft.com> wrote in message
> news:0CB9A968-9755-4AEC-9069-29DDBD70C554@microsoft.com...
> 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 worksheet with several columns (~10). I am currently sorting this
> worksheet 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 worksheet. 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

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 spreadsheet 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@discussions.microsoft.com> wrote in message
news:9B9311E1-9697-410B-AFEC-C486553693EA@microsoft.com...
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@discussions.microsoft.com> wrote in message
> news:0CB9A968-9755-4AEC-9069-29DDBD70C554@microsoft.com...
> 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 worksheet with several columns (~10). I am currently sorting this
> worksheet 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 worksheet. 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