Custom sort order

V

vkorla

New Member
Joined
Dec 12, 2017
Messages
25
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]
 
Sort by date Sort by votes
Thanks again Peter, I just re-read the forum rules and saw your links how to post screenshots.

You are correct, that was on oversight on my part - product ee should be in row 5.
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Another question
vkorla said:
When both the status and Auction End Date 2 are the same, use a tertiary criterion to sort by the number of photos, in descending order.
Click to expand...
Then why have you ranked gg 8 and hh 7? Wouldn't descending order of their photos rank gg before hh?

If this is another mistake, please review all rankings and post the results actually in the final correct order rather than putting those rankings in column G. Even a table like in post 1 would do, it doesn't have to be one of the suggested methods in the forum guidelines.

If it isn't a mistake then please try to clarify further how you get the final order as I'm struggling to understand.
 
Upvote 0
Apologies for the changing requirements and lack of clarity on this, Peter. And thank you so much for continuing to help.

I added a couple of new statuses to the list. The order of the statuses is A, B, C, D, E, F, and G.

The final output should be as below:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Auction End Date[/TD]
[TD]Auction End Date 2[/TD]
[TD]Status[/TD]
[TD]Number of Photos[/TD]
[/TR]
[TR]
[TD]102 Main Street[/TD]
[TD]9/26/2019 11:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]101 Main Street[/TD]
[TD]9/26/2019 11:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]121 Main Street[/TD]
[TD]9/27/2019 10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]103 Main Street[/TD]
[TD][/TD]
[TD]9/27/2019 10:00[/TD]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]104 Main Street[/TD]
[TD]9/27/2019 13:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]122 Main Street[/TD]
[TD]9/28/2019 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]113 Main Street[/TD]
[TD][/TD]
[TD]9/29/2019 0:00[/TD]
[TD]A[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]123 Main Street[/TD]
[TD]9/29/2019 10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]114 Main Street[/TD]
[TD][/TD]
[TD]9/30/2019 0:00[/TD]
[TD]A[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]109 Main Street[/TD]
[TD][/TD]
[TD]9/27/2019 0:00[/TD]
[TD]B[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]110 Main Street[/TD]
[TD][/TD]
[TD]9/28/2019 0:00[/TD]
[TD]B[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]124 Main Street[/TD]
[TD]10/1/2019 11:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]120 Main Street[/TD]
[TD][/TD]
[TD]10/4/2019 0:00[/TD]
[TD]B[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]115 Main Street[/TD]
[TD][/TD]
[TD]10/1/2019 0:00[/TD]
[TD]C[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]116 Main Street[/TD]
[TD][/TD]
[TD]10/1/2019 0:00[/TD]
[TD]C[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]117 Main Street[/TD]
[TD][/TD]
[TD]10/1/2019 0:00[/TD]
[TD]C[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]125 Main Street[/TD]
[TD]10/1/2019 11:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]111 Main Street[/TD]
[TD][/TD]
[TD]9/29/2019 0:00[/TD]
[TD]D[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]112 Main Street[/TD]
[TD][/TD]
[TD]9/29/2019 0:00[/TD]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]107 Main Street[/TD]
[TD][/TD]
[TD]9/26/2019 0:00[/TD]
[TD]E[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]108 Main Street[/TD]
[TD][/TD]
[TD]9/27/2019 0:00[/TD]
[TD]E[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]118 Main Street[/TD]
[TD][/TD]
[TD]10/2/2019 0:00[/TD]
[TD]F[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]119 Main Street[/TD]
[TD][/TD]
[TD]10/3/2019 0:00[/TD]
[TD]F[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]100 Main Street[/TD]
[TD][/TD]
[TD]9/24/2019 0:00[/TD]
[TD]G[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]106 Main Street[/TD]
[TD][/TD]
[TD]9/25/2019 0:00[/TD]
[TD]G[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]105 Main Street[/TD]
[TD][/TD]
[TD]9/25/2019 0:00[/TD]
[TD]G[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
:confused: The green pairs appear to use the previously stated tie-breaking rule. Not so for the orange pair. Can you clarify?

Excel Workbook
ABCDE
1ProductAuction End DateAuction End Date 2StatusNumber of Photos
2102 Main Street9/26/2019 11:002
3101 Main Street9/26/2019 11:001
4121 Main Street9/27/2019 10:002
5103 Main Street9/27/2019 10:00A1
6104 Main Street9/27/2019 13:002
7122 Main Street9/28/2019 0:003
8113 Main Street9/29/2019 0:00A5
9123 Main Street9/29/2019 10:004
10114 Main Street9/30/2019 0:00A4
11109 Main Street9/27/2019 0:00B3
12110 Main Street9/28/2019 0:00B6
13124 Main Street10/01/2019 11:001
14120 Main Street10/04/2019 0:00B0
15115 Main Street10/01/2019 0:00C1
16116 Main Street10/01/2019 0:00C2
17117 Main Street10/01/2019 0:00C3
18125 Main Street10/01/2019 11:002
19111 Main Street9/29/2019 0:00D4
20112 Main Street9/29/2019 0:00D1
21107 Main Street9/26/2019 0:00E4
22108 Main Street9/27/2019 0:00E2
23118 Main Street10/02/2019 0:00F1
24119 Main Street10/03/2019 0:00F0
25100 Main Street9/24/2019 0:00G0
26106 Main Street9/25/2019 0:00G5
27105 Main Street9/25/2019 0:00G4
Sheet2
 
Upvote 0
Oh gosh, I am sorry for this (again) Peter. The three rows that have a status of C should be in the opposite order of how they currently appear, i.e., in descending order of the # of photos, so it should be 117 Main Street first, then 116 Main Street, and finally 115 Main Street since they all have the same Auction End Date 2 value.
 
Upvote 0
vkorla said:
Oh gosh, I am sorry for this (again) Peter.
Click to expand...
Does the fact that you are struggling to put them in the right order manually perhaps indicate this is a difficult task? :eek:

While we are on that subject, why are rows 13 and 18 not together?
 
Upvote 0
You could be right! Although I am confident that if we move row 18 to be just above row 13, this is is the correct final order. Is is possible to arrive at this result via formula or a macro?
 
Upvote 0
Hi Peter,

Attempting to explain this in "English":

If we were to sort a list of products that have an "Auction End Date 2" only (i.e., no products with an "Auction End Date"), they would first be sorted by "Status": A, B, C, D, E, F, G. When multiple products have the same status, use a secondary criterion of the "Auction End Date 2", to sort by that date in ascending order. If the "Auction End Date 2" is the same, use a tertiary criterion of the "Number of Photos" to sort by that in descending order.

Now, for a list like ours that contains both, products that have an "Auction End Date", and others that have an "Auction End Date 2", the desired final sort is: to preserve the order or the products that have an "Auction End Date 2", and sort in ascending order of "Auction End Date", the products that have an "Auction End Date". When the "Auction End Date" is the same, use a secondary criterion to sort in descending order of the "Number of Photos". I hope this clarifies things.
 
Upvote 0
You could give this macro a try if these assumptions are all correct.

- Values in column A are all unique
- Status values are all single characters and the priority is set in the order in the 'Const' line near the start of the code
- Number of photos in any cell will not exceed 999
- Number of rows of data will not exceed 999
- Data is in columns A:E with headings in row 1
- Column F is available to use as a helper column

If any of these assumptions is incorrect, please provide further details about that.

Code: 
Sub Sort_Em()
  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|") & 1000 - a(i, 5) & Format(i, "000"), 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|") & 1000 - a(i, 5) & Format(i, "|000"), 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|") & 1000 - a(i, 5) & Format(i, "|000"), 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

Here is the sample data in a random order:


Excel 2016
ABCDEF
1ProductAuction End DateAuction End Date 2StatusPhotos
2106 Main Street25/09/2019 0:00G5
3124 Main Street1/10/2019 11:001
4116 Main Street1/10/2019 0:00C2
5120 Main Street4/10/2019 0:00B0
6123 Main Street29/09/2019 10:004
7113 Main Street29/09/2019 0:00A5
8107 Main Street26/09/2019 0:00E4
9102 Main Street26/09/2019 11:002
10125 Main Street1/10/2019 11:002
11119 Main Street3/10/2019 0:00F0
12109 Main Street27/09/2019 0:00B3
13100 Main Street24/09/2019 0:00G0
14114 Main Street30/09/2019 0:00A4
15121 Main Street27/09/2019 10:002
16112 Main Street29/09/2019 0:00D1
17118 Main Street2/10/2019 0:00F1
18111 Main Street29/09/2019 0:00D4
19115 Main Street1/10/2019 0:00C1
20104 Main Street27/09/2019 13:002
21105 Main Street25/09/2019 0:00G4
22108 Main Street27/09/2019 0:00E2
23101 Main Street26/09/2019 11:001
24122 Main Street28/09/2019 0:003
25103 Main Street27/09/2019 10:00A1
26117 Main Street1/10/2019 0:00C3
27110 Main Street28/09/2019 0:00B6
Sheet2


.. and again after running the code above.:


Excel 2016
ABCDEF
1ProductAuction End DateAuction End Date 2StatusPhotos
2102 Main Street26/09/2019 11:002
3101 Main Street26/09/2019 11:001
4121 Main Street27/09/2019 10:002
5103 Main Street27/09/2019 10:00A1
6104 Main Street27/09/2019 13:002
7122 Main Street28/09/2019 0:003
8113 Main Street29/09/2019 0:00A5
9123 Main Street29/09/2019 10:004
10114 Main Street30/09/2019 0:00A4
11109 Main Street27/09/2019 0:00B3
12110 Main Street28/09/2019 0:00B6
13125 Main Street1/10/2019 11:002
14124 Main Street1/10/2019 11:001
15120 Main Street4/10/2019 0:00B0
16117 Main Street1/10/2019 0:00C3
17116 Main Street1/10/2019 0:00C2
18115 Main Street1/10/2019 0:00C1
19111 Main Street29/09/2019 0:00D4
20112 Main Street29/09/2019 0:00D1
21107 Main Street26/09/2019 0:00E4
22108 Main Street27/09/2019 0:00E2
23118 Main Street2/10/2019 0:00F1
24119 Main Street3/10/2019 0:00F0
25100 Main Street24/09/2019 0:00G0
26106 Main Street25/09/2019 0:00G5
27105 Main Street25/09/2019 0:00G4
Sheet2
 
Upvote 0
Hi Peter,

Thank you so much! I can work with all of the assumptions stated, however, 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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
How do I rank a table, formula or macro please?
Replies
5
Views
123
Peter_SSs
Peter_SSs
JenniferMurphy
  • Question Question
Are these macros the best way to make custom sorts easily accessible?
Replies
2
Views
89
JenniferMurphy
JenniferMurphy
A
  • Question Question
IF condition on table with multiple criteria
Replies
1
Views
67
Ammara143
A
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
83
Peter_SSs
Peter_SSs
T
  • Solved
Can I sort a table by applying columns in a different order to the sort request?
Replies
5
Views
191
TheRedCardinal
T

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top