IF you downloaded before 12PM EST (10 FEB 06) please redownload... I forgot a function!
Pros:
- Sort using natural sort order (10 comes after 9!)
- Sort on cell color
- Sort on font color
- Sort on more than 3 columns or column colors at a time
- Casse sensitive option (all sort columns or individually)
- Ascending or descending sorts on any individual column
- Keeps many cell formating features (cell color, font color, indent
level, number format)
Cons:
- Slower than excel native sort (mainly in applying the formatting...
sorting is actually pretty quick).
- Can't sort horizontal (I have no need to do this).
- Cell formatting has no options (unless you edit the script!)
Install:
- Download the file and change the extension from "txt" to "xla"
- Put the xla file where you can find it.
- Open Excel and go to Tools -> Add Ins.
- A GUi will appear. Go down the list until you find "NaturalSort" and
put a check in the box next to it. Click ok.
- A toolbar should load with an icon and the word "sort."
Usage/Syntax:
There are three main ways to enter the sort and they depend on your current
selection. Click the sort button on the new toolbar.
* If you have one cell selected, the script assumes you want to select the
entire used sheet (sorting one cell would be pretty dumb!).
- An input box comes up that asks for the number of header rows. This is
your chance to NOT sort the rows at the top of your sheet! If you have 4
rows of header and your information starts on row 5, enter a 4 in the
box. If you have no header rows, enter a 0. The script will check every
column to determine the last USED column and last USED row. These, then,
define the selection.
- The main sort input box comes up... see below.
* If you have selected ONE row with multiple columns, you are asked for the
number of header rows as above, then the script chooses the max number
of used rows in JUST those columns. The main sort input box comes up...
see below.
* If you have selected a region that is more than one row, the sort only
applies to the selection and the main sort input box comes up... see below.
The Main Sort Input Box.
This is where you decide how you want things sorted. The syntax is as
follows:
a,b,c or A,B,C or 1,2,3 are all acceptable column designations. Just be
sure they are comma delimited!! The order you put them in determines sort
order.
@ :tells the script to ignore formatting... sorts data values ONLY! This
symbol can go anywhere in the input string (beginning or end make sense,
but are not necessary).
^ :tells the script to sort Descending (ascending is default). If this
symbol is BEFORE the ENTIRE string (ie, ^a,d,f) then descending sort is
applied to ALL sort columns. If the symbol is placed AFTER an INDIVIDUAL
column (ie, a,d^,f,i^), it applies ONLY to that column.
% :tells the script to NOT sort case sensitively. It follows the same
conventions as ^ above with regard to all sort columns vs individual
sort columns.
(a) :tells the script to sort on the cell color in column "a."
{a} :tells the script to sort on the font color in column "a."
Some Syntax examples:
@k,(a)^,b
Ignore formatting, sort on k, descending sort on a's cell color, sort on b
(a),{a},%a
Sort on a's cell color, a's font color, and a's case-insensitive value
a,b,c,d,e,f,g,h,i,j
Sort on a bunch of columns! Don't know why you'd do this... but maybe you
have a lot of data!
Support:
I will NOT be very supportive! If a bug is found I will try to correct
it. If functionality is requested that I think I would find useful I may
try to implement it. Bottom line is that this script is written for me by
me and I have "real work" (hardware engineering) to do and I make it a
point not to code at home! Feel free to modify things on your own, or ask
for how I implemented things. I will try to help there.
Credit:
I must give credit to one site in particular since I used their VBA
quicksort routine... then modified it to work with my natural sort
algorithm. Specifics are changed but the overall structure is the same.
Go to
http://www.vba-programmer.com/ and do a search for quicksort. To
sort on more than 4 columns I iteratively call the Two Column Quicksort (I
ALWAYS have one column numbered in the current sort order so I don't lose
current order (ie, if I sort on the same columns back-to-back I get the
same sort... without this numbering scheme I get a different sort
everytime due to how the quicksort works.).).
And the Add-In...
Recent Posts