topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Friday March 29, 2024, 3:49 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: VBA (Excel) - Natural Sort Add-In  (Read 22294 times)

kfitting

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 593
    • View Profile
    • Donate to Member
VBA (Excel) - Natural Sort Add-In
« on: February 10, 2006, 09:01 AM »
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...

« Last Edit: February 10, 2006, 10:59 AM by kfitting »

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 40,896
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: VBA (Excel) - Natural Sort Add-In
« Reply #1 on: February 10, 2006, 09:02 AM »
looks wonderful for those who code excel.

one suggestion: zip it up with your excellent post comments as a readme.txt

the forum will accept zip attachments and it will be helpful for people to have the readme as a file.

kfitting

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 593
    • View Profile
    • Donate to Member
Re: VBA (Excel) - Natural Sort Add-In
« Reply #2 on: February 10, 2006, 10:56 AM »
Done mouser, thanks for the tips.  Also, I realized I forgot a crucial function so if you downloaded and it gives an error... please try again!

Also, just to avoid confusion, this plug-in is NOT strictly for coders.  Anyone who has a lot of data in Excel files and wants to sort will find this useful.  It's supposed to be user-friendly... supposed to be!

Kevin
« Last Edit: February 10, 2006, 10:59 AM by kfitting »

KaysLover

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 26
    • View Profile
    • Donate to Member
Re: VBA (Excel) - Natural Sort Add-In
« Reply #3 on: February 13, 2006, 05:09 AM »
I get the message 'Internet Explorer was not able to open this Internet Site. The requested site is either unavailable or cannot be found' when I click to download the ZIP file!

Any suggestions?





Regards

KaysLover

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 40,896
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: VBA (Excel) - Natural Sort Add-In
« Reply #4 on: February 13, 2006, 05:17 AM »
I get the message 'Internet Explorer was not able to open this Internet Site. The requested site is either unavailable or cannot be found' when I click to download the ZIP file!

strange.. working for me.. anyone else having this trouble?

mrainey

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 439
    • View Profile
    • Website
    • Donate to Member
Re: VBA (Excel) - Natural Sort Add-In
« Reply #5 on: February 13, 2006, 07:01 AM »
No problem downloading.
Software For Metalworking
http://closetolerancesoftware.com

KaysLover

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 26
    • View Profile
    • Donate to Member
Re: VBA (Excel) - Natural Sort Add-In
« Reply #6 on: February 14, 2006, 02:48 AM »
I am still have the same problem downloading, any ideas?
Regards

KaysLover

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 40,896
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: VBA (Excel) - Natural Sort Add-In
« Reply #7 on: February 14, 2006, 03:53 AM »
kl, can you try downloading it with another browser like firefox and see if that works, so i can try to figure out if its the server or if its your browser causing the trouble.

KaysLover

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 26
    • View Profile
    • Donate to Member
Re: VBA (Excel) - Natural Sort Add-In
« Reply #8 on: February 14, 2006, 04:01 AM »
Mouser,

I do not have access to firefox at home or work. I am currently use IE 6,
Regards

KaysLover

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 40,896
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: VBA (Excel) - Natural Sort Add-In
« Reply #9 on: February 14, 2006, 04:05 AM »
i've uploaded it here: https://www.donation...SortAddin-060210.zip

but i sure would like to figure out why it's not downloading for you from this page.

KaysLover

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 26
    • View Profile
    • Donate to Member
Re: VBA (Excel) - Natural Sort Add-In
« Reply #10 on: February 14, 2006, 04:07 AM »
Mouser,

Thanks, managed to download from https://www.donation...SortAddin-060210.zip.

Thanks for your help.
Regards

KaysLover