Refined list sorting by substring integer after alphabetical sorting-CodePudding

I have some information in a list (called listLines). Each line below is in a List(Of String).

1|This is just a header
3|This is just a footer
2|3456789|0000000|12312312313|BLUE|1|35.00
2|7891230|0000000|45645645655|BLUE|1|22.00
2|7891230|0000000|45645645658|RED|2|13.00
2|3456789|0000000|12312312316|RED|2|45.00
2|3456789|0000000|12312312317|YELLOW|5|-9.00
2|3456789|0000000|12312312315|ORANGE|3|15.00
2|7891230|0000000|45645645659|YELLOW|5|32.00
2|3456789|0000000|12312312314|GREEN|4|-20.00
2|7891230|0000000|45645645656|GREEN|4|39.00
2|7891230|0000000|45645645657|ORANGE|3|-18.50

I'm doing a listLines.sort() on the list to sort it alphabetically. Below is what I get after the .sort().

1|This is just a header
2|3456789|0000000|12312312313|BLUE|1|35.00
2|3456789|0000000|12312312314|GREEN|4|-20.00
2|3456789|0000000|12312312315|ORANGE|3|15.00
2|3456789|0000000|12312312316|RED|2|45.00
2|3456789|0000000|12312312317|YELLOW|5|-9.00
2|7891230|0000000|45645645655|BLUE|1|22.00
2|7891230|0000000|45645645656|GREEN|4|39.00
2|7891230|0000000|45645645657|ORANGE|3|-18.50
2|7891230|0000000|45645645658|RED|2|13.00
2|7891230|0000000|45645645659|YELLOW|5|32.00
3|This is just a footer

With that said, I need to output this information to a file. I'm able to do this ok. I still have a problem though. There is a sequence number in the above data at position 5 just after the listed colors (RED, BLUE, ETC..) that you can see. It's just before the last value which is a decimal type.

I need to further sort this list, keeping it in alphabetical order since position 2 is an account number and I want to keep the account numbers grouped together. I just want them to be resorted in sequential order based on the sequence number.

I was looking at another thread trying to figure out how I can do this. I found a piece of code like listLines.OrderBy(Function(q) q.Substring(35)).ToArray. I think this would probably help me if this was a fixed length file, it isn't however. I was thinking I can do some kind of .split() to get the 5th piece of information and sort it but then it's going to unalphabetize and mix the lines back up because I don't know how to specify to still keep it alphabetical.

Right now I'm outputting my alphabetical list like below so I can format it with commas and double quotes.

    For Each listLine As String In listLines
        strPosition = Split(listLine, "|")
        Dim i As Integer = 1
        Dim iBound As Integer = UBound(strPosition)
        Do While (i <= iBound)
            strOutputText = strOutputText & Chr(34) & strPosition(i) & Chr(34) & ","
            i  = 1
        Loop

My main question is how do I re-sort after .sort() to then get each account (position1) in sequential order (position 5)? OR EVEN BETTER, how can I do both at the same time?

CodePudding user response：

The List(Of T) class has an overload of the Sort method that takes a Comparison(Of T) delegate. I would suggest that you use that. It allows you to write a method or lambda expression that will take two items and compare them any way you want. In this case, you could do that like this:

Dim items = New List(Of String) From {"1|This Is just a header",
                                      "3|This Is just a footer",
                                      "2|3456789|0000000|12312312313|BLUE|1|35.00",
                                      "2|7891230|0000000|45645645655|BLUE|1|22.00",
                                      "2|7891230|0000000|45645645658|RED|2|13.00",
                                      "2|3456789|0000000|12312312316|RED|2|45.00",
                                      "2|3456789|0000000|12312312317|YELLOW|5|-9.00",
                                      "2|3456789|0000000|12312312315|ORANGE|3|15.00",
                                      "2|7891230|0000000|45645645659|YELLOW|5|32.00",
                                      "2|3456789|0000000|12312312314|GREEN|4|-20.00",
                                      "2|7891230|0000000|45645645656|GREEN|4|39.00",
                                      "2|7891230|0000000|45645645657|ORANGE|3|-18.50"}

items.Sort(Function(x, y)
               Dim xParts = x.Split("|"c)
               Dim yParts = y.Split("|"c)

               'Compare by the first column first.
               Dim result = xParts(0).CompareTo(yParts(0))

               If result = 0 Then
                   'Compare by the second column next.
                   result = xParts(1).CompareTo(yParts(1))
               End If

               If result = 0 Then
                   'Compare by the sixth column last.
                   result = xParts(5).CompareTo(yParts(5))
               End If

               Return result
           End Function)

For Each item In items
    Console.WriteLine(item)
Next

If you prefer a named method then do this:

Private Function CompareItems(x As String, y As String) As Integer
    Dim xParts = x.Split("|"c)
    Dim yParts = y.Split("|"c)

    'Compare by the first column first.
    Dim result = xParts(0).CompareTo(yParts(0))

    If result = 0 Then
        'Compare by the second column next.
        result = xParts(1).CompareTo(yParts(1))
    End If

    If result = 0 Then
        'Compare by the sixth column last.
        result = xParts(5).CompareTo(yParts(5))
    End If

    Return result
End Function

and this:

items.Sort(AddressOf CompareItems)

Just note that this is rather inefficient because it splits both items on each comparison. That's not a big deal for a small list but, if there were a lot of items, it would be better to split each item once and then sort based on those results.