PDA

View Full Version : VBA sorting method



doubtfire
08-24-2009, 06:08 PM
I have tried to sort the worksheet name in sequence, but the result is still not in sequence expected. I got the result Sheet1, Sheet11, Sheet2 ....and the Sheet11 should be greater than the Sheet2. Is it ? Could anyone help .
What is the fastest method to do sorting under VBA.
Please help :banghead:

geekgirlau
08-24-2009, 09:00 PM
The issue is that "Sheet11" is a string - VBA is not sorting the numerical value at the end of the sheet name. You need to convert everything after "Sheet" to a number, then sort that.

I'd also consider naming the sheets to something meaningful, particularly if you have 11 or more!

Can you post the code you have so far?

doubtfire
08-25-2009, 05:27 AM
Thanks for the reply.
I use the Worksheet Name (in Uppercase) to compare within a bubble sort.
Also, what is the best and efficient way to sort and I know there is a lot of sorting methods under VBA.
Thank you.

geekgirlau
08-25-2009, 09:06 PM
A bubble sort will work, but only if your sheets are named "Sheet01", "Sheet02" etc. So you have a couple of options:

Rename your sheets to incorporate the zero for any number less than 10;
Adjust your code so that prior to performing the bubble sort it is applying the leading zero to the sheet name for any number less than 10, then removing the leading zero again after the sort.

doubtfire
08-26-2009, 08:47 AM
Thanks again.
I believe the other way is to rename the worksheet meaninfully WITHOUT numbers.
Do you have any knowledge of counting/count sort, which I read from the Internet is the best and fastest of all the sorting in VBA?:beerchug:

mdmackillop
08-26-2009, 09:14 AM
QuickSort does just what it says. Depending upon number of items, you may find little perceptible difference in performance. There is a Wiki item here (http://en.wikipedia.org/wiki/Quick_sort)

doubtfire
08-26-2009, 09:50 AM
Thanks for the QUICKsort solution.
If the number of rows is closed to a million (Excel 2007), which sorting way you think is the best of all.

mdmackillop
08-26-2009, 10:07 AM
No idea really, I've tried it using the Excel sort function which took about 6 seconds.

doubtfire
08-26-2009, 04:04 PM
Thanks again.
Last question, do you any ideas or knowledge of a sorting method - counting/count sort, which seems to be the fastest (when compare with bubble sort) when using together with VBA. Any ideas !?:friends:

agarwaldvk
08-26-2009, 08:24 PM
Hi Doubtfire

There is no readymade solution provide in VBA Excel to sort arrays.

Yes, you are right in saying that Counting sort (also sometimes called Radix Sort) is lightning quick but it is very difficult to code - particularly the MSD version of it. The LSD version is easier to code but requires strings of the same length. It performs declines dramatically for nearly sorted lists.

Some of the techniques that might help you are :-

Quick Sort - this is in place sorting using recursion. Can get stack overflow problems for multiple level sorting. Performance is O(N squared) worst case, O(N) best case.
Merge Sort - this is not in place sorting - doesn't necessarily use recursion and is relatively easy to code. Performance is generally good - O(N)

Best solution in either case (my prefernce is for Merge Sort given that the memory is cheap, effort is not) and then use Insertion sort when the list get shorter than 10 items.

However, Excel built-in sort is extremely smart - I doubt whether you would be able to code arrays using VBA coding to get performance anywhere near Excel built-in sort functionality unless you talking more than a million rows of data - in which case you might be wanting to use a database.

Whilt I know that I haven't given you the code for anything (if you insist, I can - mine is included as a part of an Utility Package that I built, so it might not be a good starting point for you but nevertheless), but hopefully this might give you the pointer to the course that you might want to take!


Alternatively, assuming that your sheet names all end in numbers, read the sheet name one at a time. Read the first sheet name character by character, starting from the left end and stop and the first numeric character. Concatenate each of the characters including this character into a new string. Convert this new string in to its numerical equivlant using the Val() function and load it into the array as the first element in the array. Repeat this for all the sheet names loading the corresponding values in the array as the next element. From here, you may either dump the array contents in to an Excel worksheet and sort it or sort the array itself - I am presuming here that your total data set would be small to very small and even an inefficient sort algorithm such as bubble sort would do the job reasonably well. Append you text prefix to these sorted numbers and you will have an array with the sorted sheet names. Hopefully this would help to some extent.
Best regards


Deepak Agarwal

doubtfire
08-27-2009, 07:48 AM
A detailed response to my worksheet name sorting, which is somehow acceptable and correspondingly I have to notify my "customers" to act the same way.
Second, in terms of sorting method, I would love to learn more of what you mentioned and please attach it/them for this hungry man.
Thanks again !!!!:friends:

bestm
12-28-2009, 05:53 PM
I have a question on using the Quicksort method.

How do I go about using the command to sort a list that is not in any particular order but want sort the order(s) per an alphabetic described list.

Let's say I want the list to show in ascending order but starting with the S's first, followed by M's, F's and B's. Any other first letters of the items would follow the B's likewise in ascending order.

TIA, this sort stuff kills me everytime,
Mike

mikerickson
12-28-2009, 07:01 PM
At the core of a Quick (or bubble) Sort routine is a statement
If a < b ThenIf the normal comparitive < doesn't do what you want, you can replace it with your own custom LT function (think LessThan).
With the conditions that you describe, you might use
Function LT(aString, bString, Optional CaseInsensitive As Boolean) As Boolean
Dim initialOrder As String
initialOrder = "SMFB"
Select Case InStr(1, initialOrder & aString, Left(aString, 1), -CaseInsensitive) - InStr(1, initialOrder & bString, Left(bString, 1), -CaseInsensitive)
Case Is < 0
LT = True
Case Is = 0
If CaseInsensitive Then aString = LCase(aString): bString = LCase(bString)
LT = aString < bString
End Select
End FunctionThis applies the S, M, F, T to only the first character of the string (i.e. "Sam".LT."Small") if you want all characters to be sorted on a revised alphabet ("Small" .LT. "Sam") a different LT function would have to be written.