Custom Sort - problem with a comma in an entry

[zookeepertx]

Hello all!

I'm trying to set up a custom sort in VBA, but I ran into a problem. I'm establishing the array to use for the sort and it's fine except that one of the items being sorted is called "Invoice, Commercial" and it's affecting the sort, since there are commas separating the array elements. Here's the full custom list:

"Broker's Invoice,Bill of Lading,Cargo Release,Invoice,Commercial,Packing List,CF 7501,Rated Invoice,Delivery Order,Receiving,Payment (Debit) Advice,FWS3177,CITES,NMG Audit,Checklist,CF 7501-REVISED", _
DataOption:=xlSortNormal

Also, VBA doesn't seem to want to let me wrap that long list to encompass 2-3 lines in the code. Does it just HAVE to string out way to the right or is there some way other than space/underscore/enter?

Thank you!

Jenny

 

[zookeepertx]

'Adjust the value of the other elements to account for this change

I'm just not sure what elements you're referring to there. My brain is locked up and I'm just
getting mad at the whole project at this point.

And, I had a macro that did part of what I wanted: a horizontal sort before the regular
(vertical) sort. Yesterday, the first sort was working and the second was kind of working,
but had the problem of the "Invoice, Commercial" not sorting out right.

But now that I've messed with it, it won't even do THAT! In fact, all it'll do right now is
create and name 2 new sheets after the original sheet! This is not a HUGELY complicated macro;
it shouldn't be this hard! I feel like an idiot!

Jenny

 

[MoshiM]

I'm just not sure what elements you're referring to there. My brain is locked up and I'm just
getting mad at the whole project at this point.

And, I had a macro that did part of what I wanted: a horizontal sort before the regular
(vertical) sort. Yesterday, the first sort was working and the second was kind of working,
but had the problem of the "Invoice, Commercial" not sorting out right.

But now that I've messed with it, it won't even do THAT! In fact, all it'll do right now is
create and name 2 new sheets after the original sheet! This is not a HUGELY complicated macro;
it shouldn't be this hard! I feel like an idiot!

Jenny

For the line in question, the comma delimited array is created with 1 more column than necessary due to the single rogue comma. Since we know the position of this comma and hence the position of every element in the array we can concatenate the preceding and next elements of the array, place a comma in between and store the string inside an array element. Since we now have an element that is the combination of two others, one of the other array elements is now unneeded while everything else after the rogue comma is in the wrong place. Using the position of the element after the rogue comma we then loop from that position up until the last minus 1. Array elements are then shifted to account for this change. The redim preserve line that I used will remove the now unnecessary final column from the original array.
FYI the most recent code I supplied has the changes I mentioned in of my earlier posts

 

[zookeepertx]

Thank you so much for your clear and understandable explanation! The only thing I had to change was to add ".Sort" right after "With WK" near the very end of the code. (It didn't like ".SetRange" without that.)
However, it's not sorting Col D in the order specified by the array. As an example, one of the sections is sorting like this:

Bill of Lading

Broker's Invoice

CF 7501

Invoice, Commercial

Packing List

Payment (Debit) Advice

Receiving

Spec Sheet

<tbody>
</tbody>

(This one doesn't happen to have a "Cargo Release", "Rated Invoice", "Delivery Order", "FWS3177", etc. Some of the sections will have a variety of them and some will have ones that are NOT on the array list. We don't care about those additional ones; they can sort in any order they want.) By the way - how is your coding telling the sheet to sort by A (normal sort), B (normal sort, C (normal sort), D ( CUSTOM SORT) and then E (normal sort)? That's the way I WANT it to sort, and that's the way it IS sorting but I don't see it in your code. No doubt it's right in front of me and I'm just not seeing it.

<tbody>
</tbody>

Thank you for your help and your patience!

Jenny

 

[shg]

An alternative would be to put the desired sort order in a list on a (perhaps hidden) worksheet, add a column to the table with an INDEX/MATCH formula, and sort of that.

 

[shg]

Then ...

Sub zktx()
  Dim wks           As Worksheet

  Set wks = Worksheets("Audit-raw data-trimmed (2)")

  With wks.Range("A1:P39")  ' col P contains the index/match formula
    .Sort Key1:=.Range("C1"), _
          Key2:=.Range("P1"), _
          Key3:=.Range("E1"), _
          Header:=xlYes, _
          MatchCase:=False
    .Sort Key1:=.Range("A1"), _
          Key2:=.Range("B1"), _
          Header:=xlYes
  End With
End Sub

 

[MoshiM]

Thank you so much for your clear and understandable explanation! The only thing I had to change was to add ".Sort" right after "With WK" near the very end of the code. (It didn't like ".SetRange" without that.)
However, it's not sorting Col D in the order specified by the array. As an example, one of the sections is sorting like this:

Sorry I didn't notice that how your sorting for D was different
Add this at the top of the procedure

Dim Name_D_Exception() As String

Then place the following somewhere before the sorting block

Name_D_Exception = Split("Broker's Invoice%Bill of Lading%Cargo Release%Invoice,Commercial%Packing List%CF 7501%Rated Invoice%Delivery Order%Receiving%Payment (Debit) Advice%FWS3177%CITES%NMG Audit%Checklist%CF 7501-REVISED", "%")

Then under Case 68 change Name_K to Name_D_Exception

To answer you question, if you reference an ASCII table(https://www.techonthenet.com/ascii/chart.php) and use the chr() function then you can loop through letters using the numbers under Dec as an input to the function. In the code below 65 to 69 when placed in the chr() function returns all the capital letters from A to E. So then I looped those letters but used a select case statement to do something specific for when X=68 which would be chr(69)="D" and then case else for every other value of X. After that you just sub in range(Chr(X) & 2 & ":" & Chr(X) & 39) to define the range.

For X = 65 To 69

    Select Case X
    
        Case 68
            Wk.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:=Name_D_Exception, DataOption:=xlSortNormal
        Case Else
            Wk.Sort.SortFields.Add2 _
            Key:=Range(Chr(X) & 2 & ":" & Chr(X) & 39), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
    
    End Select
    
Next X

Now that I think about it I could have just used a different delimiter than a comma to separate your list and then NameK=split("I1%IN,Y,%GG", "%") would have worked instead of shifting and concatenating the elements

 

[zookeepertx]

An alternative would be to put the desired sort order in a list on a (perhaps hidden) worksheet, add a column to the table with an INDEX/MATCH formula, and sort of that.

Unfortunately, this isn't a "rolling report" and there's not a template. It's newly created each time it's needed.

 

[MoshiM]

Sorry I didn't notice that how your sorting for D was different
Add this at the top of the procedure

Dim Name_D_Exception() As String

Then place the following somewhere before the sorting block

Name_D_Exception = Split("Broker's Invoice%Bill of Lading%Cargo Release%Invoice,Commercial%Packing List%CF 7501%Rated Invoice%Delivery Order%Receiving%Payment (Debit) Advice%FWS3177%CITES%NMG Audit%Checklist%CF 7501-REVISED", "%")

Then under Case 68 change Name_K to Name_D_Exception

I forgot something else which may or may not be necessary

[COLOR=#000000][FONT=SFMono-Regular]Application.AddCustomList Listarray:=Name_D_Exception[/FONT][/COLOR]

 

[shg]

Jenny,

In your code, you create a custom list,

...=Array("Broker's Invoice", _
        "Bill of Lading", "Cargo Release", "Invoice", "Commercial", "Packing List", _
        "CF 7501", "Rated Invoice", "Delivery Order", "Receiving", "Payment (Debit) Advice", _
        "FWS3177", "CITES", "NMG Audit", "Checklist", "CF 7501-REVISED")

... but then don't use it:

CustomOrder:= _
        "Broker's Invoice,Bill of Lading,Cargo Release,Invoice,Commercial,Packing List,CF 7501,Rated Invoice,Delivery Order,Receiving,Payment (Debit) Advice,FWS3177,CITES,NMG Audit,Checklist,CF 7501-REVISED", _
        DataOption:=xlSortNormal

Also, the array has a problem:

...=Array("Broker's Invoice", _
        "Bill of Lading", "Cargo Release", [COLOR="#FF0000"]"Invoice", "Commercial"[/COLOR], "Packing List", _
        "CF 7501", "Rated Invoice", "Delivery Order", "Receiving", "Payment (Debit) Advice", _
        "FWS3177", "CITES", "NMG Audit", "Checklist", "CF 7501-REVISED")

That's two items, not one item with a comma. It should be:

= Array("Broker's Invoice", "Bill of Lading", "Cargo Release", _
              [COLOR="#FF0000"]"Invoice, Commercial"[/COLOR], "Packing List", "CF 7501", "Rated Invoice", _
              "Delivery Order", "Receiving", "Payment (Debit) Advice", _
              "FWS3177", "CITES", "NMG Audit", "Checklist", "CF 7501-REVISED")

So I tried that, and verified that the custom list was correct, but it still didn't work; the item with the comma sorts to the bottom (in Excel 2010). That seems like a flat-out bug.

So, reverting to the prior suggestion, assuming columns P and Q are available,

Sub zktx()
  Dim wks           As Worksheet
  Dim avs           As Variant
  Dim r As Range

  avs = Array("Broker's Invoice", "Bill of Lading", "Cargo Release", _
              "Invoice, Commercial", "Packing List", "CF 7501", "Rated Invoice", _
              "Delivery Order", "Receiving", "Payment (Debit) Advice", _
              "FWS3177", "CITES", "NMG Audit", "Checklist", "CF 7501-REVISED")

  Set wks = Worksheets("Audit-raw data-trimmed (2)")

  With wks.Range("A1:P39")  ' col P contains the index/match formula
    Set r = .Columns(.Columns.Count + 1)
    r.Resize(UBound(avs) + 1) = Application.Transpose(avs)
    .Columns(.Columns.Count).FormulaR1C1 = Replace("=match(rC4, @, 0)", "@", r.Address(, , xlR1C1))
    .Sort Key1:=.Range("E1"), _
          Key2:=.Range("P1"), _
          Header:=xlYes, _
          MatchCase:=False
    .Sort Key1:=.Range("A1"), _
          Key2:=.Range("B1"), _
          Key3:=.Range("C1"), _
          Header:=xlYes
  End With
  Range("P:Q").ClearContents
End Sub

... and that works.

 

[shg]

That last line should be

wks.Range("P:Q").ClearContents