Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bubblesort
Is there a command called Bubblesort? If so what can it do and its functions
like multiple rows, sort a range by ascendind or descending. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bubblesort
"Bubblesort" is a relatively simple algorithm for sorting an array with
code. There's no command for it, you need to roll your own (search this group for examples). If you want to sort a range of cells use Excel's sort, record a macro to get the syntax and see "sort" in VBA help. Regards, Peter T "Philosophaie" wrote in message ... Is there a command called Bubblesort? If so what can it do and its functions like multiple rows, sort a range by ascendind or descending. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bubblesort
I don't think there's a command in excel.
It's a generic term for a way of sorting a list - compare item 1 to item 2, if they're back to front then reverse the order. Compare item 2 to item 3, then item 3 to item 4 etc. You keep going through from item 1 to item n, comparing pairs, until you can go from 1 to n without swapping any over. It's not a very efficient way of sorting but is easy to code and to scale up Sam "Philosophaie" wrote: Is there a command called Bubblesort? If so what can it do and its functions like multiple rows, sort a range by ascendind or descending. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bubblesort
On Nov 2, 5:28*am, Philosophaie
wrote: Is there a command called Bubblesort? If so what can it do and its functions like multiple rows, sort a range by ascendind or descending. John Walkenbach's "Excel 2003 Power Programming with VBA" (and probably the 2007 version as well) has an interesting table comparing various sorting algorithms. As an example of the sort of inefficiency that Sam Wilson describes, Walkenbach ran a test where it took bubblesort 329.24 seconds to sort a randomly generated list of 50,000 numbers but took another algorithm called quicksort only 0.27 seconds to sort the same list. If it is ranges that you want to sort then Peter T's suggestion of turning on the macro recorder when using Excel's built-in Data/sort is the way to go. Even if you want to sort VBA arrays a practical way is to first transfer the data into a worksheet, sort it there, and transfer it back to an array. Despite all of the data transfer going on this still outperforms boublesort except for the smallest arrays and is almost as efficient as quicksort. See Walkenbach for more details. Hope that helps -John Coleman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bubblesort
If you're sorting a range, you could just use range.sort (like using Data|Sort
in Excel's UI). If you have an array, then you could use bubblesort. John Walkenbach has an example of bubblesort in a routine that eliminates duplicates and then sorts that single array and puts the sorted values into a listbox. http://spreadsheetpage.com/index.php..._in_a_listbox/ Philosophaie wrote: Is there a command called Bubblesort? If so what can it do and its functions like multiple rows, sort a range by ascendind or descending. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bubblesort
Here's a bubble sort that I used for a two dimensional array. Hope it helps.
Sub SortList(CustList) 'CustList is the 2 dimensional array passed to the Sub. It was in fact a 'list of customer account numbers and their company names Dim Temp1 As String Dim Temp2 As String Dim First As Integer Dim Last As Long Dim i As Long Dim j As Long 'This sub sorts the list into alphabetical order by first of the numbers in the array ' using the bubble sort algorithm Dim Temp Last = UBound(CustList, 2) For i = 1 To Last For j = i + 1 To Last If CustList(1, i) CustList(1, j) Then Temp1 = CustList(1, j) Temp2 = CustList(2, j) CustList(1, j) = CustList(1, i) CustList(2, j) = CustList(2, i) CustList(1, i) = Temp1 CustList(2, i) = Temp2 End If Next j Next i End Sub If this helps, please click Yes "Sam Wilson" wrote: I don't think there's a command in excel. It's a generic term for a way of sorting a list - compare item 1 to item 2, if they're back to front then reverse the order. Compare item 2 to item 3, then item 3 to item 4 etc. You keep going through from item 1 to item n, comparing pairs, until you can go from 1 to n without swapping any over. It's not a very efficient way of sorting but is easy to code and to scale up Sam "Philosophaie" wrote: Is there a command called Bubblesort? If so what can it do and its functions like multiple rows, sort a range by ascendind or descending. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VBA - Sort a table with BubbleSort | Excel Programming |