Sort 2-dimensional Array (without external tools)

[dtaylor]

I need to sort a 2-dimensional array on multiple fields.

The source data is gathered from an internal website and placed into a 2D array as it is gathered and is not from an input file such as a CSV. I can't depend on a machine having Excel loaded and I don't really want to have to write the data to a file in order to sort it using DBQuery.

I could take the individual 2D array columns and merge them into a 1D array with field delimiters, sort the array and then split the 1D array back into a 2D array, but that seems like a lot of coding and I'm not absolutely sure that it would work as i envision in my head. This logic might require building the 1D array with fixed field lengths rather than field delimiters in order to sort correctly. I'm just not sure.

Does anyone have a way to sort a 2D array on multiple columns within MS and without external tools? If not, maybe this could be added to the wish list.

Many thanks!

[dtaylor]

I guess that I should take the lack of any responses as an answer that there really isn't an easy way to do this within Macro Scheduler without using 3rd party tools.

[JRL]

Don't give up yet... I've been working on it but its not easy. This isn't perfect but its what I have now.

As-is it uses a short data list and is fairly fast. I have also pulled my data from a thousand row 18 column excel file and it takes minutes to sort so might be too cumbersome for your liking. the premise is using the sort capabilities of a dialog memo field. The row data is converted into a long string with filler data to make each field in each row the same length. This lets the sort occur across all cells but also requires time to evaluate each cell for its length and equal time to append the filler data to each cell and concatenate into a fixed length row. The sort takes place quite quickly. Then the data has to be reorganized to remove the filler data and reinsert commas between the individual data cells.

Sorry the explanation is even cumbersome. Sorting takes place initially prior to display. After data displays you can resort by dragging any column to a new position by grabbing the column header cell. Try it see what you think.

Code: Select all

//If there is a header, Header=1
Let>Header=1

//Sorting dialog
Dialog>SortDialog
object SortDialog: TForm
  object SortBox: tMSListBox
    Sorted = True
  end
end
EndDialog>SortDialog

//Filler character low on ascii table
VBEval>chr(1),vFiller


//Creating some sample data via LabelToVar
LabelToVar>DataToSort,vInfo
Separate>vInfo,crlf,vRow


//Create "1" based, two dimensional array
//Not needed if you already have a 2D array
GoSub>MakeArray
//Get max field length
GoSub>GetMaxLength
GoSub>Sort


Dialog>Dialog1
object Dialog1: TForm
  Left = 468
  Top = 194
  HelpContext = 5000
  AutoSize = True
  BorderIcons = [biSystemMenu]
  Caption = 'View Data'
  ClientHeight = 208
  ClientWidth = 469
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = True
  ShowHint = True
  OnTaskBar = False
  PixelsPerInch = 96
  TextHeight = 13
  object MSStringGrid1: tMSStringGrid
    Left = 0
    Top = 0
    Width = 469
    Height = 208
    BiDiMode = bdLeftToRight
    Color = 16777200
    Ctl3D = False
    DoubleBuffered = False
    DrawingStyle = gdsGradient
    FixedCols = 0
    Font.Charset = DEFAULT_CHARSET
    Font.Color = clWindowText
    Font.Height = -11
    Font.Name = 'MS Sans Serif'
    Font.Style = []
    GradientEndColor = 16777200
    GradientStartColor = clSilver
    Options = [goFixedHorzLine, goVertLine, goHorzLine, goRangeSelect, goColSizing, goColMoving, goFixedColClick, goFixedRowClick, goFixedHotTrack]
    ParentBiDiMode = False
    ParentCtl3D = False
    ParentDoubleBuffered = False
    ParentFont = False
    TabOrder = 0
  end
end
EndDialog>Dialog1


SetDialogProperty>Dialog1,MSStringGrid1,LoadFromCSV,vData
SetDialogProperty>Dialog1,MSStringGrid1,ColCount,vItem_Count
AddDialogHandler>Dialog1,MSStringGrid1,OnColumnMoved,ReSort

Show>Dialog1,


/*
DataToSort:
Last,First,County,Age
Jones,Adam,Sioux,62
Smith,Robert,Sioux,42
Jones,Samantha,Howard,53
Johnson,Tom,Sioux,65
Jones,Adam,Sioux,30
Jones,Polly,Franklin,32
Smith,John,Franklin,44
Smith,Abby,Howard,23
Jones,Steve,Howard,37
*/


SRT>ReSort
  GetDialogProperty>Dialog1,MSStringGrid1,SaveToCSV,vData
  Separate>vData,crlf,vRow
  GoSub>MakeArray
  GoSub>GetMaxLength
  GoSub>Sort
  SetDialogProperty>Dialog1,MSStringGrid1,LoadFromCSV,vData
END>ReSort

SRT>MakeArray
  Let>aa=0
  Repeat>aa
    Add>aa,1
    Let>Val=vRow_%aa%
    Separate>Val,comma,vItem
    Let>bb=0
    Repeat>bb
      Add>bb,1
      Let>value=vItem_%bb%
      Let>Cell_%aa%_%bb%=value
    Until>bb=vItem_Count
  Until>aa={%vRow_Count%-1}
END>MakeArray

SRT>GetMaxLength
  Let>MaxLen=0
  Let>LL=0
  Repeat>LL
    Add>ll,1
    Let>MM=0
    Repeat>MM
      Add>MM,1
      Let>Value=Cell_%LL%_%MM%
      Length>Value,vLen
      If>vLen>MaxLen
        Let>MaxLen=vLen
      EndIf
      If>MaxLen_%mm%={"MaxLen_%mm%"}
        Let>MaxLen_%mm%=vLen
      Else
        Let>mLen=MaxLen_%mm%
        If>vLen>mLen
          Let>MaxLen_%mm%=vLen
        EndIf
      EndIf
    Until>MM=vItem_Count
  Until>LL={%vRow_Count%-1}
END>GetMaxLength

SRT>Sort
  Let>vData=
  If>Header=1
    Let>kk=1
  Else
    Let>kk=0
  EndIf
  Repeat>kk
    Let>vLine=
    Add>kk,1
    Let>val=vRow_%kk%
    Let>FF=0
    Separate>Val,comma,cell
    Repeat>ff
      Add>ff,1
      Let>value=cell_%FF%
      Trim>value,value
      Let>value=%value%%vFiller%
      MidStr>value,1,MaxLen,value
      Let>vLine=%vLine%%value%#
    Until>ff=cell_Count
    Let>vData=%vData%%vLine%%crlf%
  Until>kk={%vRow_Count%-1}

  SetDialogProperty>SortDialog,SortBox,Text,vData
  GetDialogProperty>SortDialog,SortBox,Text,vData
  If>Header=1
    Let>vData=%vRow_1%%crlf%%vData%
  EndIf
  StringReplace>vData,vFiller,,vData
  StringReplace>vData,#,%comma%,vData
END>Sort

[dtaylor]

Many thanks JRL!

I was not familiar with the sort capabilities using a dialog memo field.

This is perfect as the data I need to sort is not huge. It is more important that I can perform the sort without needing external tools and speed is not an import factor in this particular case.

I really appreciate the time you spent to provide such a great solution! You are a rock star to me!