Custom sort order

[vkorla]

I have a list of products that need to be sorted in a very specific manner - the final sort order should be as described in the column titled "Overall Rank".

The problem I have is that I need to first sort the products by the column titled "Date". Then, for a subset of the products (namely E, F, G, H, I, J and K), I need these to be sorted first by the "Status" column as some statuses take precedence over others, even if that results in the final list not being purely sorted by date. The order of the products that should be sorted by status is defined in the column titled "Status Rank".

The column titled "Overall Rank" is what the final sort order should be.

How can I accomplish this?

Thanks in advance!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Date[/TD]
[TD]Status[/TD]
[TD]Number of Photos[/TD]
[TD]Status Rank[/TD]
[TD]Overall Rank[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9/26/19 10:00[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9/26/19 10:00[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9/27/19 10:00[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]9/28/19 10:00[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]9/29/19[/TD]
[TD]L[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]9/30/19[/TD]
[TD]M[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]10/1/19[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]10/1/19[/TD]
[TD]O[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]10/3/19[/TD]
[TD]P[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]10/3/19[/TD]
[TD]Q[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]9/24/19[/TD]
[TD]R[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]

 

[vkorla]

As an FYI, I do not ever anticipate having a list of more than 6000 or 7000 rows, if that makes any difference...

 

[Peter_SSs]

.. curious as to why there appears to be a limit of 999 rows of data? Is there a way to make this work with a few thousand rows?

It is just the way I wrote the code & is easy to change. Changing the 3 blue sections below from originally having three 0s to each having four 0s will allow you to go to 9999 rows. Adding a fifth 0 would allow you to go to 99999 rows.

While making this change I did notice a couple of problems with my code that *could* cause it to order the rows incorrectly.

- Needed to add the red "|" you see below in front of that first set of blue 0s.
- Three places I should have used 999 instead of 1000
- The assumption about the maximum number of photos is also incorrect - it should be 899 not 999. I'm assuming you will have nothing like that in a single cell but if you could we can adjust the code for that as well by adding more 9s to those three 999 values.


Should be more robust now.

Sub Sort_Em_v2()
  Dim a As Variant, b As Variant
  Dim SL As Object
  Dim i As Long, j As Long
  Dim s As String
  Dim bInserted As Boolean
  
  Const StatusPriority As String = "ABCDEFG"
  
  a = Range("A2:E" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a), 1 To 1)
  Set SL = CreateObject("System.Collections.Sortedlist")
  For i = 1 To UBound(a)
    If Len(a(i, 4)) Then SL.Add Format(InStr(StatusPriority, a(i, 4)), "000|") & Format(a(i, 3), "0.000000|") & 999 - a(i, 5) & Format(i, "|0000"), a(i, 1)
  Next i
  For i = 1 To UBound(a)
    If Len(a(i, 2)) Then
      bInserted = False
      For j = 0 To SL.Count - 1
        s = SL.GetKey(j)
        If Round(a(i, 2), 6) - Split(s, "|")(1) < 0 Then
          SL.Add Left(s, 4) & Format(a(i, 2), "0.000000|") & 999 - a(i, 5) & Format(i, "|0000"), a(i, 1)
          bInserted = True
          Exit For
        End If
      Next j
      If Not bInserted Then SL.Add Left(SL.GetKey(SL.Count - 1), 4) & Format(a(i, 2), "0.000000|") & 999 - a(i, 5) & Format(i, "|0000"), a(i, 1)
    End If
  Next i
  For i = 1 To UBound(b)
    b(i, 1) = SL.IndexOfValue(a(i, 1)) + 1
  Next i
  Application.ScreenUpdating = False
  Cells(2, "F").Resize(UBound(b)).Value = b
  Range("A2:F" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlNo
  Cells(2, "F").Resize(UBound(b)).ClearContents
  Application.ScreenUpdating = True
End Sub

 

[vkorla]

Thank you so much Peter! When I try to execute the macro, I see the error "Run-time error '13': Type mismatch" - did you also see this during your test?

 

[Peter_SSs]

Thank you so much Peter! When I try to execute the macro, I see the error "Run-time error '13': Type mismatch" - did you also see this during your test?

No, if I was getting an error I wouldn't have posted the code.

When you get the error & 'Debug', what line in the code is highlighted?

 

[vkorla]

Hi Peter, this line is highlighted:

If Round(a(i, 2), 6) - Split(s, "|")(1) < 0 Then

 

[Peter_SSs]

Most likely you have at least one date/time in column A that is not actually a 'proper' date/time but rather a text entry.

Run the code again and when you get to the error, Debug again.
- Hover your cursor over the i in a(i,2) in that line. Suppose the pop-up says i=4 then that tells you that the (first) problem cell is B5 (not B4)
- Now hover over the a in a(i,2) in that line. The pop-up will most likely say something like
a(i,2) = "1/10/2019 11:00:00 AM"

If so, that is the problem because if that cell was a proper date/time, there would be no quote marks around the value.

Back in the worksheet if you put the formula =ISNUMBER(A5) it will most likely return FALSE. Perhaps all your date/time values in both columns are text, not actual date/times (numbers)?

 

[vkorla]

Thanks again Peter. It appears that the problem cell in my case is B6, however, I have checked the formatting of both Auction End Date and Auction End Date 2 and they appear to be formatted correctly as date values: https://drive.google.com/open?id=14gVqKuCIn3rQ7SvdLB3GmpNhj11a2UcE

Or am I doing something wrong?

 

[Peter_SSs]

I have checked the formatting of both Auction End Date and Auction End Date 2 and they appear to be formatted correctly as date values

The cells are formatted as Date/Time, but the values in some of them are not. (You can enter text in such cells and that is what you have)
=ISNUMBER(B6) still returns FALSE.

If you widen out columns B & C you will see some values move to the right of the cells (proper Date/Times - that is, Numbers) and some values move to the left of the cells (Text).

Now that you have both columns formatted correctly, one way to force conversion of those text values is to select all of columns B:C and do a Find/Replace with Find what: M, Replace with: M, ensure 'Match entire cell contents' is not checked in Options -> Replace All

 

[vkorla]

Should I enter the value "M" in both the "Find what" and "Replace with" fields? That did not appear to do anything unfortunately

 

[Peter_SSs]

Should I enter the value "M" in both the "Find what" and "Replace with" fields?

Yes

That did not appear to do anything unfortunately

Did you check in Options>> in the Find/Replace dialog that 'Match entire cell contents' is not checked?