ArrayMultiColSort -New Release 06 April 2019

[dmob]

UDF header say error 1, extended 3 (More data rows in $aSortData than columns in $aArray), however your code:

If UBound($aSortData) > UBound($aArray) Then

checks Rows in $aSortData against Rows in $aArray ?

or am I reading it wrong

[Melba23]

dmob,

Mea culpa! Thanks for that - it will be fixed in the next release.

M23

[mistersquirrle]

On 5/9/2020 at 6:41 AM, Melba23 said:

A sensible suggestion which could have a wider application. Please try this Beta version of the UDF and see if it meets with your requirements...

M23

I tried it out, and that works just fine for my application, so thanks for taking some time. I agree that RegExs can certainly be a bit of a pain

Looking forward to more updates and scripts from you, love your stuff.

[Melba23]

Hi all,

I have added the possibility to search for other than a full match when sorting the array. There is a new parameter to determine which sort of match to make:

;                 $iMatch    - If sorting on strings, string match mode (default = 0 - full match)
;                                 - 0 = Full match of string with element
;                                 - 1 = Partial (StringInStr) match of string with element
;                                 - 2 = RegEx match with element - user must supply valid Regex patterns
;                                     Note: The selected mode applies to ALL strings matched within ALL columns

 If anyone would care to test this new Beta I would be most grateful: 

M23

 

Edited June 14, 2020 by Melba23
Beta code removed

[pixelsearch]

Hi Melba23
This new feature ($iMatch) looks very interesting, gonna test if for sure.

Meanwhile, I was wondering if the "string of items" could be reduced to a single item without producing an error. For example, here is the line found in the example script (1st page of this thread) :

Global $aSortData = [ _
    [0, 1], _
    [1, 0], _
    [2, "SSSSS,QQQQQ,PPPPP,RRRRR"], _
    [3, 0]]

As soon as you change the string to what follows, you'll get @error = 2 and @extended = 1 :

Global $aSortData = [ _
    [0, 1], _
    [1, 0], _
    [2, "SSSSS"], _
    [3, 0]]

imho one should be able to sort the column with "SSSSS" rows placed first, no matter the other rows stay unsorted. I'm asking this because a search for a partial match, like in the script below, would produce the same error :

#include "ArrayMultiColSort_Mod.au3"

Global $aArray[5][3] =  [ _
    [5, "hello", "there"], _
    [5, "bonjour", "ici"], _
    [1, "bye", "hey"], _
    [5, "autoit", "good"], _
    [5, "great", "fantastic"]]

_ArrayDisplay($aArray, "before")

Global  $iSortDirection = 0 ; 0 = ascending, 1 = descending, or string : for last column in $aSortData[][]

Global $aSortData[][] = [ _
    [0, $iSortDirection], _
    [2, "oo"]]

_ArrayMultiColSort($aArray, $aSortData, 0, 0, 1) ; last 1 = Partial (StringInStr) match of string with element
If @error Then MsgBox(0,"_ArrayMultiColSort", "@error = " & @error & "    @extended = " & @extended)

_ArrayDisplay($aArray, "after")

In this example, one could get a correct result by typing what follows... not sure it's the good way :

"oo," & chr(255)

 

[Melba23]

pixelsearch,

I see what you mean - I will look into how it might be done.

M23

[Melba23]

pixelsearch,

My apologies - I did not realise I had not posted an amended version which does what you require. Now you only get the error if you ask the UDF to sort using a blank string for a column.

Please try this out and let me know if it works for you: ArrayMultiColSort_Mod.au3

M23

[pixelsearch]

Thanks Melba23
Just tried the amended version, it seems to work fine using the following script, which is based on "partial match of string"

expandcollapse popup

#include "ArrayMultiColSort_Mod.au3"

Global $aArray[15][3] =  [ _
    [10, "Left side", "Combo 10"], _
    [11, "Middle facing", "Combo 11"], _
    [12, "Right side", "Flat 12"], _
    [15, "Left side", "Duplex 15"], _
    [5, "Middle facing", "Duplex 5"], _
    [14, "Middle facing", "Flat 14"], _
    [3, "Middle facing", "Duplex 3"], _
    [7, "Right side", "Duplex 7"], _
    [6, "Left side", "Flat 6"], _
    [13, "Right side", "Combo 13"], _
    [8, "Right side", "Combo 8"], _
    [1, "Middle facing", "Flat 1"], _
    [9, "Left side", "Duplex 9"], _
    [4, "Left side", "Duplex 4"], _
    [2, "Right side", "Duplex 2"]]

_ArrayDisplay($aArray, "Unsorted")

Global $aSortData[][] = [ _
    [1, "Middle"]]
_ArrayMultiColSort($aArray, $aSortData, 0, 0, 1) ; last 1 = Partial (StringInStr) match of string with element
If @error Then MsgBox(0,"_ArrayMultiColSort Test #1", "@error = " & @error & "    @extended = " & @extended)
_ArrayDisplay($aArray, "Middle")

Global $aSortData[][] = [ _
    [1, "Middle"], _
    [2, "Duplex"]]
_ArrayMultiColSort($aArray, $aSortData, 0, 0, 1) ; last 1 = Partial (StringInStr) match of string with element
If @error Then MsgBox(0,"_ArrayMultiColSort Test #2", "@error = " & @error & "    @extended = " & @extended)
_ArrayDisplay($aArray, "Middle & Duplex")

Global $aSortData[][] = [ _
    [1, "Middle"], _
    [2, "Duplex,Flat"]]
_ArrayMultiColSort($aArray, $aSortData, 0, 0, 1) ; last 1 = Partial (StringInStr) match of string with element
If @error Then MsgBox(0,"_ArrayMultiColSort Test #3", "@error = " & @error & "    @extended = " & @extended)
_ArrayDisplay($aArray, "Middle & Duplex,Flat")

Now I'm gonna try the RegExp part ($iMatch = 2, "RegEx match with element")
Maybe one should avoid to use the comma inside the RegEx pattern, or it will be considered as a separator within the "sort order" column of the parameter $aSortData ?

[Melba23]

pixelsearch,

At present a comma in the RegEx would be considered as a delimiter. I am not too concerned about that at the moment - unless someone else can convince me otherwise!

M23

[rlvitorino]

I use these commands, but it doesn't sort column 0, value 27 should be first.

Global $aSortData[][] = [[0, 0]]

 

Edited September 7, 2021 by Melba23
Amended font and removed images

[Melba23]

rlvitorino,

You have the "numbers" stored in string datatypes - so they are sorted alphabetically. Use Num() to convert them to numeric datatypes and they will sort as you wish.

M23

[rlvitorino]

 

I convert to number before adding array. I've concatenated another column to addArray

Number(StringStripWS($Match[0],$STR_STRIPALL))

_ArrayAdd($aResult ,Number(StringStripWS($Match[0],$STR_STRIPALL)) & "|" & StringStripWS(StringReplace(StringReplace(StringReplace($aFiles[$i],"result ",""),"(",""),").png",""), $STR_STRIPALL))
_ArrayDisplay($aResult,"")

 

Edited September 7, 2021 by Melba23
Amended font and removed images

[jchd]

PLease stop using giant font, that won't make your case easier.

33 minutes ago, rlvitorino said:

Number(StringStripWS($Match[0],$STR_STRIPALL)) & "|" &

You do use Number() but you concatenate that to "|" and some other string, so everything in the added row is a string.

Loop thru your array and convert every numeric cells using Number() explicitely.

[Melba23]

rlvitorino,

What jchd said!

M23

P.S. And please stop posting images and using giant fonts.

[auto-it-tous]

nice and fast to sort

Edited January 8, 2022 by auto-it-tous

[maniootek]

Hello. I just worked with function _ArrayMultiColSort() and found that it doesn't sort specified column properly if there is mixed datatypes (like strings and numbers). See example:

Global $aTest[][] = [ _
    [ "a","1", 1] _
    ,[ "b","4", 1] _
    ,[ "a","3", "1_test"] _
    ,[ "a","1", 2] _
    ,[ "b","6", "1_test"] _
    ,[ "a","8", 1] _
    ,[ "b","1", 1] _
    ,[ "b","0", "1_test"] _
    ,[ "a","1", 1] _
    ,[ "b","1", 2] _
    ,[ "b","1", "1_test"] _
    ,[ "a","5", 1] _
    ,[ "b","6", 2] _
    ,[ "b","7", "1_test"] _
    ,[ "a","8", 1] _
    ,[ "b","8", 1] _
    ,[ "b","8", "1_test"] _
    ,[ "a","1", 1] _
    ,[ "b","2", 2] _
    ]
_ArrayDisplay($aTest, "before sorting")
Global $g_aSortData[][] = [ [0, 0], [2, 0], [1, 0] ]
_ArrayMultiColSort($aTest, $g_aSortData)
_ArrayDisplay($aTest, "after sorting")

to fix it i had to convert all cells to string

Global $aTest[][] = [ _
    [ "a","1", 1] _
    ,[ "b","4", 1] _
    ,[ "a","3", "1_test"] _
    ,[ "a","1", 2] _
    ,[ "b","6", "1_test"] _
    ,[ "a","8", 1] _
    ,[ "b","1", 1] _
    ,[ "b","0", "1_test"] _
    ,[ "a","1", 1] _
    ,[ "b","1", 2] _
    ,[ "b","1", "1_test"] _
    ,[ "a","5", 1] _
    ,[ "b","6", 2] _
    ,[ "b","7", "1_test"] _
    ,[ "a","8", 1] _
    ,[ "b","8", 1] _
    ,[ "b","8", "1_test"] _
    ,[ "a","1", 1] _
    ,[ "b","2", 2] _
    ]
_ArrayDisplay($aTest, "before sorting")
Global $g_aSortData[][] = [ [0, 0], [2, 0], [1, 0] ]

for $x=0 to Ubound($aTest) - 1
    for $y=0 to Ubound($aTest, 2) - 1
        $aTest[$x][$y] = String($aTest[$x][$y])
    Next
Next


_ArrayMultiColSort($aTest, $g_aSortData)
_ArrayDisplay($aTest, "after sorting")

I don't know if this is a bug or this is ok. Just giving you my feedback.

[Melba23]

mandriospo,

Quote

it doesn't sort specified column properly if there is mixed datatypes (like strings and numbers)

That is a "feature" of the array sort algorithm in the standard Array library which is used in the UDF - so no bug. But thanks for taking the time to point it out.

M23