# Your thoughts on sorting!



## Lewiy (Jul 31, 2008)

Recently I’ve been involved in a number of Excel projects that have required sorting of data within macros, specifically on hidden worksheets.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
As far as I know, the built-in VBA sort method only works on an active sheet which is less than ideal when you are working with hidden sheets.  Yes, you can disable ScreenUpdating, unhide the sheet, sort the data and re-hide again, but this seems a bit of a waste of time and I’ve still experienced run time errors using this method so I’m growing weary of it.
<o> </o>
So, I’ve started investigating other methods of sorting data “manually”.  The one I’m favouring at the moment is the bubble sort because of it’s reasonably straight forward implementation.  This sort runs through each item of data, checks if each item is less than the one after it and if not, swaps them.  It runs through this process continually until it does a complete pass through the data without making any swaps.  The other benefit is that you can run this on a single column of data or even quite easily adapt it to sort a whole row based on the values in a column.  The coding is quite simple:


```

```


```

```


```
Set Control = Sheets("Sheet1")<o:p></o:p>
With Control<o:p></o:p>
    Limit = .Cells(Rows.Count, 1).End(xlUp).Row<o:p></o:p>
    Do<o:p></o:p>
        AnySwaps = False<o:p></o:p>
        For c = 1 To Limit - 1<o:p></o:p>
            If .Cells(c, 1) > .Cells(c + 1, 1) Then<o:p></o:p>
                .Cells(Limit + 1, 1) = .Cells(c, 1)<o:p></o:p>
                .Cells(c, 1) = .Cells(c + 1, 1)<o:p></o:p>
                .Cells(c + 1, 1) = .Cells(Limit + 1, 1)<o:p></o:p>
                .Cells(Limit + 1, 1) = ""<o:p></o:p>
                AnySwaps = True<o:p></o:p>
            End If<o:p></o:p>
        Next c<o:p></o:p>
    Loop Until AnySwaps = False<o:p></o:p>
End With
```

<o> </o>
However, the only problem with this is that it can take a little while to run if there is a large amount of data.  So I began looking at other methods of sorting such as the Bucket Sort, and Insertion Sort which both reduce run time, but have their issues.  With the Bucket Sort, it was reasonably easy to sort a single column of data, but doing entire rows was much more complicated because of the nature of moving items into several groups (buckets), sorting each individually and then returning them to the main list.  When using whole rows, this becomes a case of creating a worksheet for each “bucket”.  The insertion sort would be fine, except that I had to create a custom function for identifying the relative position of items compared to the rest of the list when I was dealing with text strings (because functions like RANK don’t work on text).
<o> </o>
Does anyone have any suggestions as to other sort methods that might be more efficient and/or easier to use with whole rows of data?  I’ve checked places like Wikipedia for Sort Methods, but many of these seem quite abstract and difficult to apply to an Excel environment.


----------



## RoryA (Jul 31, 2008)

You can use the built-in sort on hidden and inactive sheets, so I would do that!


----------



## Jonmo1 (Jul 31, 2008)

> As far as I know, the built-in VBA sort method only works on an active sheet


 
Says Who?

Obtained from the macro recorder (added sheet names instead of selecting).

works Just fine when Sheet2 is hidden.

```
Sub Test()
    Sheets("Sheet2").Range("A:F").Sort Key1:=Sheets("Sheet2").Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub
```


----------



## Lewiy (Jul 31, 2008)

Ok, I’ve noticed what I’ve been doing wrong all these months…….not specifying the sheet name in the Key field.  Doh!  Don’t I feel stupid now!!! <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
As an aside though, does anyone know what sort method Excel actually uses to sort data?  This sorting business has become a bit of an interest of mine recently and I’m curious to investigate other methods purely for interest’s sake!<o></o>


----------



## RoryA (Jul 31, 2008)

I don't know what Excel uses internally, but you may want to lookup the QuickSort algorithm.


----------



## Jonmo1 (Jul 31, 2008)

> Doh! Don’t I feel stupid now!!!


 
Not at all, see my signature...

As far as interest sake for other methods, the method you provided certainly works fine.  
Interesting that it sorts by capitalization as well...Capitalized letters go first.  The built in sort doesn't do that.  that's kinda cool or frustrating depending on how you look at it.

As far as making it faster, consider turning off events/screenupdating/calculation..


----------



## Domski (Jul 31, 2008)

> Don’t I feel stupid now!!!


 
I really wouldn't worry I spent an absolute age staring at a piece of code yesterday trying to work out it was misbehaving only to find I'd forgotten one full stop. I despair with myself sometimes


----------



## RoryA (Jul 31, 2008)

> I'd forgotten one full stop


if I had a penny for every time I'd done that, I'd have at least £183.17 by now.


----------



## Legacy 96851 (Jul 31, 2008)

I'm actually not sure Insertion sort (or Selection) is technically any faster than bubble sort. They're both O(n^2) in the average case. If your data is already *almost* sorted, or somehow semi-in-order, insertion sort is a solid, stable, in place method. Additionally, if you implement it with a binary search to find the place to insert data, it beats bubble sort by a little bit, although at that point, you're too deep in code for a simple problem, IMO. 
I'd be happy to tell someone else to do that for me, but I'd never do it myself.

I can't believe you jumped to BucketSort next, though; I guess if you're sorting names it's alright. It's probably just me, but I've always hated that one.

No, my friend, what you need is the tried and true, good old-fashioned, king of all sorting algorithms: the randomized QuickSort. Without going into detail describing it here, I'll simply direct you to Wikipedia, who has a great analysis of the process. 
The normal implementation requires a decent amount of extra space, so if that's a problem, there's the in place implementation to cure your woes. Then again, some people I've talked too consider it a bit abstract. I use it any time I need to sort a decent amount of data, though. It's not even too bad on the coding side of things, though I haven't had the need yet to do one in VBA, so I could be wrong.


----------



## Lewiy (Jul 31, 2008)

quote] I'm actually not sure Insertion sort (or Selection) is technically any faster than bubble sort.[/quote]<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Whether it technically is faster or not, I’m not sure, but I stuck a timer into both sorts and the Insertion sort come out significantly faster (about 75%) even though I was using a slightly customised version of it (example sorts a single column of data in column A):<o></o>


```
<o:p></o:p>
```


```

```


```
Sub CustomSort()<o:p></o:p>
Dim Limit As Long<o:p></o:p>
Dim c As Long, PutMe As Long<o:p></o:p>
Dim iStart As Double, iEnd As Double<o:p></o:p>
iStart = Time<o:p></o:p>
Limit = Cells(Rows.Count, 1).End(xlUp).Row<o:p></o:p>
Range("B:C").ClearContents<o:p></o:p>
For c = 1 To Limit<o:p></o:p>
    Cells(c, 2).Formula = "=SUMPRODUCT(--(A1:A" & Limit & "<A" & c & "),--(A1:A" & Limit & "<>""""))"<o:p></o:p>
    Cells(c, 2).value = Cells(c, 2).value + 1<o:p></o:p>
Next c<o:p></o:p>
For c = 1 To Limit<o:p></o:p>
    PutMe = Cells(c, 2)<o:p></o:p>
    Do Until Cells(PutMe, 3) = ""<o:p></o:p>
        PutMe = PutMe + 1<o:p></o:p>
    <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on">Loop</st1:place><o:p></o:p>
    Cells(PutMe, 3) = Cells(c, 1)<o:p></o:p>
Next c<o:p></o:p>
Columns("A:B").Delete<o:p></o:p>
iEnd = Time<o:p></o:p>
Cells(1, 2) = Format(iEnd - iStart, "hh:mm:ss")<o:p></o:p>
End Sub<o:p></o:p>
```
<o></o>
<o> </o>



> As far as making it faster, consider turning off events/screenupdating/calculation


<o></o>
<o> </o>
Yes, obviously I have invoked these code-speeding-up methods and to be honest, the bubble sort isn’t too bad, but it can be a little frustrating even having to wait a couple of seconds for a userform to load up when it performs the sort first to give a sorted list for a combo/listbox.<o></o>
<o> </o>
Curious that it should sort by capitalisation as well though, I hadn’t noticed that one!!<o></o>
<o> </o>


> I can't believe you jumped to BucketSort next, though; I guess if you're sorting names it's alright. It's probably just me, but I've always hated that one.


<o></o>
<o> </o>
I was actually really surprised by the bucket sort actually, I remembered covering the theory of it at college and when I came to actually implement it, I couldn’t see how it could be that much quicker, given that I was bubble sorting through each bucket once they were split up.  But sure enough, it gave me a 30-40% decrease in run time over the bubble sort on it’s own.


----------



## Legacy 96851 (Jul 31, 2008)

What am I doing wrong, here? I put a bunch of random numbers in column A, ran your code, and it... put the time in B1, and copied and pasted my numbers about 8000 rows down, without sorting anything. 

And now, trying again with different numbers, 
Do Until Cells(PutMe, 3) = ""
is causing errors, because PutMe is greater than Rows.Count.


----------



## Jonmo1 (Jul 31, 2008)

Because they're formulas.  The random part of the formula recalculated and became a new number after it was sorted.


----------



## Legacy 96851 (Jul 31, 2008)

jonmo1 said:


> Because they're formulas. The random part of the formula recalculated and became a new number after it was sorted.


 
??? I'm not using Rnd, I just banged on my keyboard.


----------



## Jonmo1 (Jul 31, 2008)

Sorry, my bad. When I read random numbers, I assumed you meant a formula...
Yes, I know what I get when I assume.......


----------



## Legacy 96851 (Jul 31, 2008)

jonmo1 said:


> Sorry, my bad. When I read random numbers, I assumed you meant a formula...
> Yes, I know what I get when I assume.......


 
You overlooked the obvious, if you will :wink:
Regardless, I can't get that sub even close to working, and I've tragically exhausted my time to play around - my data structure curiousity will have to go unsated for the day.


----------



## schielrn (Jul 31, 2008)

If you are trying to use the last thing Lewiy posted for the sort code.  It is missing part of the sumproduct formula, looks possibly due to the < error?



> Cells(c, 2).Formula = "=SUMPRODUCT(--(A1:A" & Limit & "<A" ?< & Limit ?),--(A1:A? c>


----------



## Legacy 96851 (Jul 31, 2008)

schielrn said:


> If you are trying to use the last thing Lewiy posted for the sort code. It is missing part of the sumproduct formula, looks possibly due to the < error?


 
I noticed it had issues in that line (hence the problem with PutMe being huge), I just couldn't figure out what.


----------



## Lewiy (Jul 31, 2008)

Sorry, didn't check the post obviously, the SUMPRODUCT formula is:


```

```


```

```


```
"=SUMPRODUCT(--(A1:A" & Limit & "<A" & c & "),--(A1:A" & Limit & "<>""""))"
```


----------



## Lewiy (Jul 31, 2008)

A" & c & "),--(A1:A" & Limit & "<>""""))"

With a "less than" symbol between those two bits


----------



## schielrn (Jul 31, 2008)

Try putting it in php tags or putting a space before and after the <


----------

