Sort values separated by slash

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Sort values separated by slash

Post by YasserKhalil »

Hello everyone

I have the following values in 1d array:
Untitled.png
You can read these items from right to left, I mean 5 / 1 is read as one slash five. When used bubble sort method to sort this 1d array, I got the values sorted but not as expected

Code: Select all

Sub BubbleSort(myArray() As Variant)
    Dim temp, i As Long, j As Long
    For i = LBound(myArray) To UBound(myArray) - 1
        For j = i + 1 To UBound(myArray)
            If myArray(i) > myArray(j) Then
                temp = myArray(j)
                myArray(j) = myArray(i)
                myArray(i) = temp
            End If
        Next j
    Next i
End Sub
If I put the values in one column in excel and use the original sort within excel, excel asked me to choose how to deal with numbers and I select the first option (sort anything that looks like numbers, as a number) and this option gives me the exact output. How can I modify the udf to be able to do the same as for sorting the values
Untitled.png
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78957
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sort values sepatarted by slash

Post by HansV »

I haven't tested it, but does it work if you change

Code: Select all

            If myArray(i) > myArray(j) Then
to

Code: Select all

            If CDate(myArray(i)) > CDate(myArray(j)) Then
Best wishes,
Hans

User avatar
SpeakEasy
5StarLounger
Posts: 600
Joined: 27 Jun 2021, 10:46

Re: Sort values sepatarted by slash

Post by SpeakEasy »

So what Excel does is it examines the string, and tries to figure out what sort of number it is,. In this case, perhaps surprisingly, it thinks it has dates , and is sorting on that. Now, if that is the output you need, then you just need to change your bubble sort a bit ...

If cdate(myArray(i)) > cdate(myArray(j))Then

This should give you the same order as you show in your second image

Be careful, though, CDATE is not quite what happens on the worksheet itself - works well enough for this example, but you'll get a mismatch if you have say "1/14" in one of the cells. Of course if you really want it to emulate the worksheet - why not stick the array into a temporary worksheet. sort that, and return the result into an array?

User avatar
SpeakEasy
5StarLounger
Posts: 600
Joined: 27 Jun 2021, 10:46

Re: Sort values separated by slash

Post by SpeakEasy »

Ah, I see Hans beat me to it

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Re: Sort values separated by slash

Post by YasserKhalil »

Thank you very much. CDate does the trick.