(Solved) How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?
(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.
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.
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.
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
#endmacro
Type udt
As Long year
As Long Day
As Long Hour
As String Name
End Type
'set up a sort for each required field
SetQsort(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 With
Next
End Sub
Sub 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
Next
Print "Press a key . . ."
Print
Sleep
End Sub
Dim 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)")
print
print "Done"
sleep
Re: How to sort datasheet (x-dim array) by a few columns ?
dodicat is way too fast. I was trying to make something similar.
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.
(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)).
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")
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)).
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):
Note - assuming multisort "Sort by year+day" will has different result then "Sort by day+year", IIRC, by my app.
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, procedures
Type 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() ...
'main
Dim As udt x(1 To 10)
Fill(x())
'set default flags
breakSorts=0
inkey=""
'control loop
while 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 if
wend
? "Sorts breaked, ending.."
sleep
end
Re: How to sort datasheet (x-dim array) by a few columns ?
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.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
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 ?
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)
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).
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
#endmacro
Type udt
As Long year
As Long Day
As Long Hour
As String Name
End Type
'set up a sort for each required field
SetQsort(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 With
Next
End Sub
Sub 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
Next
Print "Press a key . . ."
Print
Sleep
End Sub
Dim 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)")
print
print "Done"
sleep
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
#endmacro
Type udt
As Long year
As Long Day
As Long Hour
As String Name
End Type
'set up a sort for each required field
SetQsort(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 With
Next
End Sub
Sub 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
Next
Print "Press a key . . ."
Print
Sleep
End Sub
Dim 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")
print
print "Done"
sleep
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.
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 0
End Function
#endmacro
Type udt
As Long year(any)
As Long Day(any)
As Long Hour(any)
As String Name(any)
End Type
Sub 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))
Next
End Sub
Sub 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)
Next
Print "Press a key . . ."
Print
Sleep
End Sub
dim as udt x
dim as long max=20 'number of entries/elements
redim 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)
print
print "Done"
sleep
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 string
end type
operator row_type.cast () as string
return str(year_) & ", " & str(day_) & ", " & str(hour_) & ", " & bla1 & ", " & bla2
end operator
function 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 0
end function
const NUM_ROWS = 10000
dim shared as row_type row(NUM_ROWS - 1)
randomize timer
'fill with random data
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
next
print "Unsorted data:"
print "--- First 5 items ---"
for i as integer = 0 to 4
print row(i)
next
print "--- Last 5 items ---"
for i as integer = NUM_ROWS - 5 to NUM_ROWS - 1
print row(i)
next
qsort(@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)
next
print "--- Last 5 items ---"
for i as integer = NUM_ROWS - 5 to NUM_ROWS - 1
print row(i)
next
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:
Edit: Code updated, shared var removed / fixed.
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 string
end type
operator row_type.cast () as string
return str(year_) & ", " & str(day_) & ", " & str(hour_) & ", " & bla1 & ", " & bla2
end operator
enum E_DATA_ROW
E_DATA_YEAR
E_DATA_DAY
E_DATA_HOUR
end enum
type 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 long
end type
dim as E_DATA_ROW data_type.sortOrder(0 to 2)
constructor data_type(numRows as integer)
redim row(numRows - 1)
end constructor
destructor data_type()
erase row
end destructor
sub 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
next
end sub
sub 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)
next
end sub
sub 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 sub
function 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 0
end function
dim as data_type myData = data_type(10000)
randomize timer
myData.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()
Last edited by badidea on Dec 02, 2019 23:04, edited 1 time in total.
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) .
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.
Code: Select all
rem URL=http://www.vbaexpress.com/forum/showthread.php?41938-Solved-sort-multi-dimensional-array&s=bcfeb8d8b80ebc815cbe73dfe36a81c9
Sub 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
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.
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/ ... ort.c.html
Here I use a matrix type representation of rows and columns.
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/ ... ort.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 0
End Function
#endmacro
#define range(f,l) int(Rnd*(((l)+1)-(f)))+(f)
'=================
SetCsort(Long,Qsortlongcallback,up,)
SetCsort(String,Qsortstringcallback,down,)
'================
randomize
Sub 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
Print
End Sub
Sub 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)
Next
End Sub
Sub 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
Print
End Sub
Sub 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)
Next
End Sub
Dim As Long a(1 To 6,1 To 7) '6 rows 7 columns
dim as string b(1 To 12,1 To 6) '12 rows 6 columns
'------fill up both randomly
For x As Long=1 To Ubound(a,1)
For y As Long=1 To Ubound(a,2)
a(x,y)=10+Rnd*20
Next
Next
For 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)
Next
Next
'--------------------
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")
Next
print "Press a key . . ."
sleep
show(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")
Next
print "Press a key to end . . ."
sleep
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.
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.
Re: How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?
@ badidea
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 ?
Just in this, I would expect no sort, but it sorts by year, seems..
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 !
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) '...fail
myData.sort(E_DATA_HOUR,0,0) '...works !
myData.printSome():sleep
print !"\nmyData.sort(DAY,HOUR):"
rem myData.sort(E_DATA_DAY, E_DATA_HOUR) '...fail
myData.sort(E_DATA_DAY, E_DATA_HOUR,0) '...works !
myData.printSome():sleep
Code: Select all
print "Unsorted data :"
rem myData.sort() '...fail
myData.sort(0,0,0) '...works, but always sort by year !
myData.printSome():sleep
(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 !