## (Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

General FreeBASIC programming questions.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### (Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Hi,

my question is, howto do this in FB. When I am doing datasheet sort by 3 columns in Office, it's quite fast.
But I have no idea, howto do it 'FB smart'.
Ordinary solution is clear, assuming also slow.My interest is about some 'dependant sorting algo' ? Multisort ??
Could somebody to help me, please?
Say, the first column is year, second is day, third is hour, the rest are raw data (mixed types).Datasheet can be taken as one multidim UDT array, or a few vectors, for simplicity.Big datasheet FB sort by 1 column is working perfectly for me - note.
Last edited by ppf on Dec 07, 2019 13:10, edited 2 times in total.
dodicat
Posts: 6644
Joined: Jan 10, 2006 20:30
Location: Scotland

### Re: How to sort datasheet (x-dim array) by a few columns ?

If you use a udt array holding the information then quicksort would do.
You must set each sort, give it a name, direction and field.

Code: Select all

` #define up <,>#define down >,<#macro SetQsort(datatype,fname,b1,b2,dot)    Sub fname(array() As datatype,begin As Long,Finish As Long)    Dim As Long i=begin,j=finish    Dim As datatype x =array(((I+J)\2))    While  I <= J        While array(I)dot b1 X dot:I+=1:Wend        While array(J)dot b2 X dot:J-=1:Wend                If I<=J Then Swap array(I),array(J): I+=1:J-=1    Wend    If J > begin Then fname(array(),begin,J)    If I < Finish Then fname(array(),I,Finish)    End Sub#endmacroType udt    As Long year    As Long Day    As Long Hour    As String NameEnd Type'set up a sort for each required fieldSetQsort(udt,Qsortyear,up,.year)SetQsort(udt,Qsortday,up,.day)SetQsort(udt,Qsorthour,up,.hour)SetQsort(udt,Qsortname,down,.name)Sub Fill(x() As udt)    Dim As String s(1 To 5)={"Peter","Paul","Mary","Jim","Jean"}    Randomize 1    For n As Long=1 To Ubound(x)    With x(n)        .year=2000+Rnd*30        .day=1+Rnd*7        .hour=Rnd*24        .name=s(Int(1+Rnd*5))    End WithNextEnd SubSub printout(x() As udt,msg As String="")    Print Ucase(msg)     Print "year";Tab(10);"day";Tab(20);"hour";Tab(30);"name"     Print    For n As Long=1 To Ubound(x)  Print x(n).year;Tab(10);x(n).day;Tab(20);x(n).hour;Tab(30);x(n).name      NextPrint "Press a key . . ."PrintSleepEnd SubDim As udt x(1 To 20)Fill(x())printout(x(),"raw array")qsortyear(x(),Lbound(x),Ubound(x))printout(x(),"Sort by year")qsortday(x(),Lbound(x),Ubound(x))printout(x(),"Sort by day")qsorthour(x(),Lbound(x),Ubound(x))printout(x(),"Sort by hour")qsortname(x(),Lbound(x),Ubound(x))printout(x(),"Sort by name (downwards)")printprint "Done"sleep `
Posts: 2122
Joined: May 24, 2007 22:10
Location: The Netherlands

### Re: How to sort datasheet (x-dim array) by a few columns ?

dodicat is way too fast. I was trying to make something similar.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: How to sort datasheet (x-dim array) by a few columns ?

Hi, dodicat,

really thanks for quick reply and impressive example !
(Your usage of macros is simply magic for me.)

But what is right syntax for multisort of array in your example, say "year and (then) hour" ? I don't see it..
My try tests give me couple of errors.
Trying this, e.g.

Code: Select all

`qsortyearhour(x(),Lbound(x),Ubound(x))printout(x(),"Sort by year andalso hour")`

(My mind is fighting with your code, as I use slightly different way for real huge UDT array (1 column sort) in my app.
There I do no swaps of udt, whole array is constant all the time.I do temporary copy of column to sort, then sorted taggedly with its indexes - sortedID(i).
(Printed) Result is not simple indexing - a(i), but double indexing - a(sortedID(i)).
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Seeing & compare on my old app and your example.Principially code very similar, nice.
My 3 paged control loop needs face lifting ;)) to 3 lines loop.Yeah.

Thinking from end to beginning on paper; to get procedure prototypes.

It could looks like this (pseudocode, procedure names first shot):

Code: Select all

`   'types, macros, proceduresType udt ...macro SetQsort(datatype,fname,b1,b2,dot) ...Sub Fill(x() As udt) ...Sub printout(x() As udt,msg As String="") ...Sub qSortMulti() ...Sub qSortTagged() ...   'mainDim As udt x(1 To 10)Fill(x())   'set default flagsbreakSorts=0inkey=""   'control loopwhile not breakSorts            'flag to exit from all sorts  if inkey/multikey .. then                            'change/select sort/multisort or set exit flag   'selectSortCols()                     'set sort criteria - by which cols and order   'makeMultiSort()                           'call qsortMulti()   selectSortCols(setOrderPlusCols())                     'set sort criteria - by which cols and order   makeMultiSort(setOrderPlusCols())                    'call qsortMulti()   printout(x(),"Sort by : ",setOrderPlusCols())                    'print datasheet  end ifwend? "Sorts breaked, ending.."sleepend`

Note - assuming multisort "Sort by year+day" will has different result then "Sort by day+year", IIRC, by my app.
jj2007
Posts: 1625
Joined: Oct 23, 2016 15:28
Location: Roma, Italia
Contact:

### Re: How to sort datasheet (x-dim array) by a few columns ?

dodicat wrote:If you use a udt array holding the information then quicksort would do.
You must set each sort, give it a name, direction and field

OP wants to sort a spreadsheet first by column C, then by column A, then by column B, etc., as does Excel. I am afraid QuickSort is not the right one for that task - you need a stable sort, like MergeSort.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Exactly. For huge array I use CombSort and MergeSort. For example here it is not important, I think, which kind of sort algo is used for demonstration of solution, is'n it ?
dodicat
Posts: 6644
Joined: Jan 10, 2006 20:30
Location: Scotland

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Thanks jj2007.
Still using quicksort, you can adjust slightly to sort columns one by one, but still storing the data in a udt.
(Simply by swapping only the field)

Code: Select all

`#define up <,>#define down >,<#macro SetQsort(datatype,fname,b1,b2,dot)    Sub fname(array() As datatype,begin As Long,Finish As Long)    Dim As Long i=begin,j=finish    Dim As datatype x =array(((I+J)\2))    While  I <= J        While array(I)dot b1 X dot:I+=1:Wend        While array(J)dot b2 X dot:J-=1:Wend                If I<=J Then Swap array(I)dot,array(J)dot: I+=1:J-=1  'swap the field only  -- only change to previous code    Wend    If J > begin Then fname(array(),begin,J)    If I < Finish Then fname(array(),I,Finish)    End Sub#endmacroType udt    As Long year    As Long Day    As Long Hour    As String NameEnd Type'set up a sort for each required fieldSetQsort(udt,Qsortyear,up,.year)SetQsort(udt,Qsortday,up,.day)SetQsort(udt,Qsorthour,up,.hour)SetQsort(udt,Qsortname,down,.name)Sub Fill(x() As udt)    Dim As String s(1 To 5)={"Peter","Paul","Mary","Jim","Jean"}    Randomize 1    For n As Long=1 To Ubound(x)    With x(n)        .year=2000+Rnd*30        .day=1+Rnd*7        .hour=Rnd*24        .name=s(Int(1+Rnd*5))    End WithNextEnd SubSub printout(x() As udt,msg As String="")    Print Ucase(msg)     Print "year";Tab(10);"day";Tab(20);"hour";Tab(30);"name"     Print    For n As Long=1 To Ubound(x)  Print x(n).year;Tab(10);x(n).day;Tab(20);x(n).hour;Tab(30);x(n).name      NextPrint "Press a key . . ."PrintSleepEnd SubDim As udt x(1 To 20)Fill(x())printout(x(),"raw array")qsortyear(x(),Lbound(x),Ubound(x))printout(x(),"Sort by year")qsortday(x(),Lbound(x),Ubound(x))printout(x(),"Sort by day")qsorthour(x(),Lbound(x),Ubound(x))printout(x(),"Sort by hour")qsortname(x(),Lbound(x),Ubound(x))printout(x(),"Sort by name (downwards)")printprint "Done"sleep  `

I have used quicksort for many years, found no problems so far.
However, you can create a suitable macro for a sort of your choice.
For example combsort (Which I don't like, and it is slower).

Code: Select all

` #define up >#define down <#macro SetQsort(datatype,fname,dirn,dot)    Sub fname(array() As datatype,begin As Long,Finish As Long)            var size=Ubound(array),switch=0,j=0            Dim As Single void=size            Do                void=void/1.3: If void<1 Then void=1                switch=0                For i As Integer =1 To size-void                    j=i+void                    If array(i)dot dirn array(j)dot Then                         Swap array(i)dot,array(j)dot: switch=1                    End If                Next            Loop Until  switch =0 And void=1        End Sub#endmacroType udt    As Long year    As Long Day    As Long Hour    As String NameEnd Type'set up a sort for each required fieldSetQsort(udt,Qsortyear,up,.year)SetQsort(udt,Qsortday,up,.day)SetQsort(udt,Qsorthour,up,.hour)SetQsort(udt,Qsortname,down,.name)Sub Fill(x() As udt)    Dim As String s(1 To 5)={"Peter","Paul","Mary","Jim","Jean"}    Randomize 1    For n As Long=1 To Ubound(x)    With x(n)        .year=2000+Rnd*30        .day=1+Rnd*7        .hour=Rnd*24        .name=s(Int(1+Rnd*5))    End WithNextEnd SubSub printout(x() As udt,msg As String="")    Print Ucase(msg)     Print "year";Tab(10);"day";Tab(20);"hour";Tab(30);"name"     Print    For n As Long=1 To Ubound(x)  Print x(n).year;Tab(10);x(n).day;Tab(20);x(n).hour;Tab(30);x(n).name      NextPrint "Press a key . . ."PrintSleepEnd SubDim As udt x(1 To 20)Fill(x())printout(x(),"raw array")qsortyear(x(),Lbound(x),Ubound(x))printout(x(),"Sort by year  combsort")qsortday(x(),Lbound(x),Ubound(x))printout(x(),"Sort by day combsort")qsorthour(x(),Lbound(x),Ubound(x))printout(x(),"Sort by hour combsort")qsortname(x(),Lbound(x),Ubound(x))printout(x(),"Sort by name (downwards) combsort")printprint "Done"sleep `
dodicat
Posts: 6644
Joined: Jan 10, 2006 20:30
Location: Scotland

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Here is another method using the tried and tested c runtime sort.
To sort the fields one by one the udt is made to hold array fields.
I have added another female name (gender equality)
Example.

Code: Select all

`#include once "crt.bi"'=========  set up c sort =========#define up <,>#define down >,<#define ArrayToSort(x,start,finish) @X((start)),((finish)-(start)+1),Sizeof(X)#macro SetCSort(Datatype,FnName,b1,b2,dot)Function FnName Cdecl(n1 As Any Ptr,n2 As Any Ptr) As long    If *Cptr(Datatype Ptr,n1)dot b1 *Cptr(Datatype Ptr,n2)dot Then Return -1    If *Cptr(DataType Ptr,n1)dot b2 *Cptr(DataType Ptr,n2)dot Then Return 1    return 0End Function#endmacroType udt    As Long year(any)    As Long Day(any)    As Long Hour(any)    As String Name(any)End TypeSub Fill(x As udt,max as long)    Dim As String s(1 To 6)={"Peter","Paul","Mary","Jim","Jean","Anne"}    Randomize 1    For n As Long=1 To max        x.year(n)=2000+Rnd*30        x.day(n)=1+Rnd*7        x.hour(n)=Rnd*24        x.name(n)=s(Int(1+Rnd*6))NextEnd SubSub printout(x As udt,msg As String="",max as long=20)    Print Ucase(msg)     Print "year";Tab(10);"day";Tab(20);"hour";Tab(30);"name"     Print    For n As Long=1 To max  Print x.year(n);Tab(10);x.day(n);Tab(20);x.hour(n);Tab(30);x.name(n)      NextPrint "Press a key . . ."PrintSleepEnd Subdim as udt xdim as long max=20 'number of entries/elementsredim x.year(1 to max)redim x.day(1 to max)redim x.hour(1 to max)redim x.name(1 to max)'set up a sort for each required field array (In reality, only one callback function is required for long)SetCsort(long,Qsortyearcallback,up,)SetCsort(long,Qsortdaycallback,up,)SetCsort(long,Qsorthourcallback,up,)SetCsort(string,Qsortnamecallback,down,)Fill(x,max)printout(x,"raw array")qsort(arraytosort(x.year,Lbound(x.year),Ubound(x.year)),@Qsortyearcallback)printout(x,"Sort by year",max)qsort(arraytosort(x.day,Lbound(x.day),Ubound(x.day)),@Qsortdaycallback)printout(x,"Sort by day",max)qsort(arraytosort(x.hour,Lbound(x.hour),Ubound(x.hour)),@Qsorthourcallback)printout(x,"Sort by hour",max)qsort(arraytosort(x.name,Lbound(x.name),Ubound(x.name)),@Qsortnamecallback)printout(x,"Sort by name (downwards)",max)printprint "Done"sleep  `
Posts: 2122
Joined: May 24, 2007 22:10
Location: The Netherlands

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

This is what I made with qsort:

Code: Select all

`#include "crt/stdlib.bi"type row_type   dim as ushort year_, day_, hour_   dim as string bla1, bla2   declare operator cast () as stringend typeoperator row_type.cast () as string   return str(year_) & ", " & str(day_) & ", " & str(hour_) & ", " & bla1 & ", " & bla2end operatorfunction qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as long   if pRow1->year_ < pRow2->year_ then return -1   if pRow1->year_ > pRow2->year_ then return +1   'years equal   if pRow1->day_ < pRow2->day_ then return -1   if pRow1->day_ > pRow2->day_ then return +1   'days equal   if pRow1->hour_ < pRow2->hour_ then return -1   if pRow1->hour_ > pRow2->hour_ then return +1   'hours equal   return 0end functionconst NUM_ROWS = 10000dim shared as row_type row(NUM_ROWS - 1)randomize timer'fill with random datafor i as integer = 0 to ubound(row)   with row(i)      .year_ = int(rnd * 100) + 1950      .day_ = int(rnd * 365) + 1      .hour_ = int(rnd * 24)      .bla1 = string(int(rnd * 10) + 1, int(rnd * 26) + asc("a"))      .bla2 = string(int(rnd * 10) + 1, int(rnd * 26) + asc("A"))   end withnextprint "Unsorted data:"print "--- First 5 items ---"for i as integer = 0 to 4   print row(i)nextprint "--- Last 5 items ---"for i as integer = NUM_ROWS - 5 to NUM_ROWS - 1   print row(i)nextqsort(@row(0), NUM_ROWS, sizeof(row_type), cptr(any ptr, @qSortCallback))print !"\nSorted data:"print "--- First 5 items ---"for i as integer = 0 to 4   print row(i)nextprint "--- Last 5 items ---"for i as integer = NUM_ROWS - 5 to NUM_ROWS - 1   print row(i)next`
Posts: 2122
Joined: May 24, 2007 22:10
Location: The Netherlands

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

A bit experimental code that allows sorting order:

Code: Select all

`#include "crt/stdlib.bi"type row_type   dim as ushort year_, day_, hour_   dim as string bla1, bla2   declare operator cast () as stringend typeoperator row_type.cast () as string   return str(year_) & ", " & str(day_) & ", " & str(hour_) & ", " & bla1 & ", " & bla2end operatorenum E_DATA_ROW   E_DATA_YEAR   E_DATA_DAY   E_DATA_HOURend enumtype data_type   static as E_DATA_ROW sortOrder(0 to 2)   dim as row_type row(any)   declare constructor(numRows as integer)   declare destructor()   declare sub initRandom()   declare sub printSome()   declare sub sort(sort1st as E_DATA_ROW, sort2nd as E_DATA_ROW, sort3rd as E_DATA_ROW)   declare static function qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as longend typedim as E_DATA_ROW data_type.sortOrder(0 to 2)constructor data_type(numRows as integer)   redim row(numRows - 1)end constructordestructor data_type()   erase rowend destructorsub data_type.initRandom()   for i as integer = 0 to ubound(row)      with row(i)         .year_ = int(rnd * 100) + 1950         .day_ = int(rnd * 365) + 1         .hour_ = int(rnd * 24)         .bla1 = string(int(rnd * 10) + 1, int(rnd * 26) + asc("a"))         .bla2 = string(int(rnd * 10) + 1, int(rnd * 26) + asc("A"))      end with   nextend subsub data_type.printSome()   print "--- First 5 items ---"   for i as integer = 0 to 4      print row(i)   next   print "--- Last 5 items ---"   for i as integer = ubound(row) - 4 to ubound(row)      print row(i)   nextend subsub data_type.sort(sort1st as E_DATA_ROW, sort2nd as E_DATA_ROW, sort3rd as E_DATA_ROW)   sortOrder(0) = sort1st   sortOrder(1) = sort2nd   sortOrder(2) = sort3rd   qsort(@row(0), ubound(row) + 1, sizeof(row_type), cptr(any ptr, @qSortCallback))end subfunction data_type.qSortCallback cdecl(pRow1 as row_type ptr, pRow2 as row_type ptr) as long   for i as integer = 0 to 2      select case sortOrder(i)      case E_DATA_YEAR         if pRow1->year_ < pRow2->year_ then return -1         if pRow1->year_ > pRow2->year_ then return +1      case E_DATA_DAY         if pRow1->day_ < pRow2->day_ then return -1         if pRow1->day_ > pRow2->day_ then return +1      case E_DATA_HOUR         if pRow1->hour_ < pRow2->hour_ then return -1         if pRow1->hour_ > pRow2->hour_ then return +1      end select   next   return 0end functiondim as data_type myData = data_type(10000)randomize timermyData.initRandom()print "Unsorted data:"myData.printSome()print !"\nmyData.sort(YEAR, DAY, HOUR):"myData.sort(E_DATA_YEAR, E_DATA_DAY, E_DATA_HOUR)myData.printSome()print !"\nmyData.sort(HOUR, DAY, YEAR):"myData.sort(E_DATA_HOUR, E_DATA_DAY, E_DATA_YEAR)myData.printSome()`

Edit: Code updated, shared var removed / fixed.
Last edited by badidea on Dec 02, 2019 23:04, edited 1 time in total.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

For web help just googled some VB forum; founded this code, plus other for sort in range.Looks like they use ordinary solution for multisort of datasheet, (when not used a built-in Excel functions) .

Code: Select all

`rem URL=http://www.vbaexpress.com/forum/showthread.php?41938-Solved-sort-multi-dimensional-array&s=bcfeb8d8b80ebc815cbe73dfe36a81c9Sub Sort_2D_Array()         Dim v As Variant    Dim i As Integer, j As Integer, ci As Integer    Dim r As Integer, c As Integer    Dim temp As Variant    'Dim data(5, 2) As Variant    Dim data() As Variant        'populate array    data = Sheet1.Range("inputarray")    'Create 2-dimensional array         'v = Array(56, 22, "xyz", 22, 30, "zyz", 56, 30, "zxz", 22, 30, "zxz", 10, 18, "zzz", 22, 18, "zxx")    'For i = 0 To UBound(v)    '    data(i \ 3, i Mod 3) = v(i)    'Next         'Bubble sort 1st column         ci = LBound(data, 2)                         '1st column index    For i = LBound(data) To UBound(data) - 1        For j = i + 1 To UBound(data)            If data(i, ci) < data(j, ci) Then                For c = LBound(data, 2) To UBound(data, 2)                    temp = data(i, c)                    data(i, c) = data(j, c)                    data(j, c) = temp                Next            End If        Next    Next         'Bubble sort 2nd column, where adjacent rows in 1st column are equal         ci = LBound(data, 2) + 1                     '2nd column index    For i = LBound(data) To UBound(data) - 1        For j = i + 1 To UBound(data)            If data(i, ci - 1) = data(j, ci - 1) Then       'compare adjacent rows in 1st column                If data(i, ci) < data(j, ci) Then                    For c = LBound(data, 2) To UBound(data, 2)                        temp = data(i, c)                        data(i, c) = data(j, c)                        data(j, c) = temp                    Next                End If            End If        Next    Next         'Output sorted array         For r = LBound(data) To UBound(data)        For c = LBound(data, 2) To UBound(data, 2)            Debug.Print data(r, c);        Next        Debug.Print    Next     End Sub`

Anyway, this finally was an inspiration for a design of solution to test:
1/ first col is sorted by MergeSort algo (or other one with equal power)
2/ all next subsorts in/by selected cols in datasheet must be a matter of amount of adjacent equal values.
T.m., approximately for 2-5 equals a BubbleSort will be used ;
approx. for 6-1000 equals a QuickSort will be used ;
approx. for 1001-up equals a MergeSort will be used.
3/ all swaps will be on tagged index range, not array values.Only temporary copy of column will be sorted.
All the job will be about indexes.
dodicat
Posts: 6644
Joined: Jan 10, 2006 20:30
Location: Scotland

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

The cqsort is a good all round sort.
array size differences are catered for, it is not a standard recursive quicksort.
Anything included in msvcrt.dll is well tried and tested/optimised I would say.
The c code for qsort is here
https://code.woboq.org/userspace/glibc/stdlib/qsort.c.html

Here I use a matrix type representation of rows and columns.

Code: Select all

`#include once "crt.bi"'=========  set up c sort =========#define up <,>#define down >,<#define ArrayToSort(x,start,finish) @X((start)),((finish)-(start)+1),Sizeof(X)#macro SetCSort(Datatype,FnName,b1,b2,dot)Function FnName Cdecl(n1 As Any Ptr,n2 As Any Ptr) As Long    If *Cptr(Datatype Ptr,n1)dot b1 *Cptr(Datatype Ptr,n2)dot Then Return -1    If *Cptr(DataType Ptr,n1)dot b2 *Cptr(DataType Ptr,n2)dot Then Return 1    Return 0End Function#endmacro#define range(f,l) int(Rnd*(((l)+1)-(f)))+(f)'=================SetCsort(Long,Qsortlongcallback,up,)SetCsort(String,Qsortstringcallback,down,) '================randomizeSub show overload(matrix() As Long,msg As String)    Print msg    For x As Long=Lbound(matrix,1) To Ubound(matrix,1)        For y As Long=Lbound(matrix,2) To Ubound(matrix,2)            Print matrix(x,y);        Next        Print    Next    PrintEnd SubSub sort overload(matrix() As Long,i As Long)    Dim As Long t(Lbound(matrix,1) To Ubound(matrix,1))    For x As Long=Lbound(t) To Ubound(t)        t(x)=matrix(x,i)    Next    qsort(arraytosort(t,Lbound(t,1),Ubound(t,1)),@Qsortlongcallback)    For x As Long=Lbound(t) To Ubound(t)        matrix(x,i)=t(x)    NextEnd SubSub show overload(matrix() As string,msg As String)    Print msg    For x As Long=Lbound(matrix,1) To Ubound(matrix,1)        For y As Long=Lbound(matrix,2) To Ubound(matrix,2)            Print matrix(x,y);        Next        Print    Next    PrintEnd SubSub sort overload(matrix() As string,i As Long)    Dim As string t(Lbound(matrix,1) To Ubound(matrix,1))    For x As Long=Lbound(t) To Ubound(t)        t(x)=matrix(x,i)    Next    qsort(arraytosort(t,Lbound(t,1),Ubound(t,1)),@Qsortstringcallback)    For x As Long=Lbound(t) To Ubound(t)        matrix(x,i)=t(x)    NextEnd SubDim As Long a(1 To 6,1 To 7)    '6 rows 7 columnsdim as string b(1 To 12,1 To 6) '12 rows 6 columns'------fill up both randomlyFor x As Long=1 To Ubound(a,1)    For y As Long=1 To Ubound(a,2)        a(x,y)=10+Rnd*20    NextNextFor x As Long=1 To Ubound(b,1)    For y As Long=1 To Ubound(b,2)        b(x,y)=chr(range(97,122),range(97,122),range(97,122),32)    NextNext'--------------------show(a(),"Original")For n As Long=Lbound(a,2) To Ubound(a,2) 'loop through all columns    sort(a(),n)    show(a(),"column "+Str(n)+" sorted")Nextprint "Press a key . . ."sleepshow(b(),"original")For n As Long=Lbound(b,2) To Ubound(b,2) 'loop through all columns    sort(b(),n)    show(b(),"column "+Str(n)+" sorted down")Nextprint "Press a key to end . . ."sleep `
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

@dodicat

Do you have your own speed comparison of sort algos used by you in projects ?
I did nothing yet about that, btw just founded on rosetta code webpage a few freebasic sort algo sources.
E.g. Heapsort, Mergesort, Quicksort - by context marked as most powerful.

This one and your sort snippets, thanks a lot for it, really appreciated !!, leads me to some detailed test
about speed on my huge datasheet.
One another minor problem founded, but I'll fix it quickly, I think.
It's about quick key input and recognition of keys sequence to determine, which cols and order is passed into multisort routine.
Real data needs speed, so I want to avoid GUI dialogs, like Excel has for cols selection.
Say, single keypress 1,2,3,4,5,6 numpad represent six independent sorts by 1 column.This works well for me.
Now I must recognize Sequence keypresses as 'shortcut' for multisort (f.e. 32461), also some break press, when typo happened.
It must be sequence, not a triple/more keypress, I think - it's not suitable.
Sequential keypress in theat case easy solves possible typo by sending 'Delete buffer' flag, ideal would be Backspace key.
ppf
Posts: 88
Joined: Oct 10, 2017 6:41

### Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?

Now tried your method.Short code, looks smart, pointers,callback.. and works.I like it.
This design is behind my pointer horizon :-(

Then tried Excel intent.Any permutation/combination of sort criteria.
Have you tried this syntax ?

Code: Select all

`print !"\nmyData.sort(HOUR):"rem myData.sort(E_DATA_HOUR)      '...failmyData.sort(E_DATA_HOUR,0,0)      '...works !myData.printSome():sleepprint !"\nmyData.sort(DAY,HOUR):"rem myData.sort(E_DATA_DAY, E_DATA_HOUR)      '...failmyData.sort(E_DATA_DAY, E_DATA_HOUR,0)         '...works !myData.printSome():sleep`

Just in this, I would expect no sort, but it sorts by year, seems..

Code: Select all

`print "Unsorted data :"rem myData.sort()      '...failmyData.sort(0,0,0)         '...works, but always sort by year !myData.printSome():sleep`

I'll expand to 5 sort cols on real data.
(My real maximum is cca 10 cols, and obviously used max 5 cols.
2-5 is normal to get correct preview about status quo).

Good job, thank you for such smart idea.Keep it forwarding !