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!

Product	Date	Status	Number of Photos	Status Rank	Overall Rank
A	9/26/19 10:00		3		2
B	9/26/19 10:00		5		1
C	9/27/19 10:00		4		3
D	9/28/19 10:00		3		4
E	9/29/19	L	2	3	7
F	9/30/19	M	1	5	9
G	10/1/19	N	1	6	10
H	10/1/19	O	3	4	8
I	10/3/19	P	0	2	6
J	10/3/19	Q	1	1	5
K	9/24/19	R	0	7	11

<tbody>
</tbody>

 

[vkorla]

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.

 

[Peter_SSs]

Another question

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.

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.

 

[vkorla]

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:

Product	Auction End Date	Auction End Date 2	Status	Number of Photos
102 Main Street	9/26/2019 11:00			2
101 Main Street	9/26/2019 11:00			1
121 Main Street	9/27/2019 10:00			2
103 Main Street		9/27/2019 10:00	A	1
104 Main Street	9/27/2019 13:00			2
122 Main Street	9/28/2019 0:00			3
113 Main Street		9/29/2019 0:00	A	5
123 Main Street	9/29/2019 10:00			4
114 Main Street		9/30/2019 0:00	A	4
109 Main Street		9/27/2019 0:00	B	3
110 Main Street		9/28/2019 0:00	B	6
124 Main Street	10/1/2019 11:00			1
120 Main Street		10/4/2019 0:00	B	0
115 Main Street		10/1/2019 0:00	C	1
116 Main Street		10/1/2019 0:00	C	2
117 Main Street		10/1/2019 0:00	C	3
125 Main Street	10/1/2019 11:00			2
111 Main Street		9/29/2019 0:00	D	4
112 Main Street		9/29/2019 0:00	D	1
107 Main Street		9/26/2019 0:00	E	4
108 Main Street		9/27/2019 0:00	E	2
118 Main Street		10/2/2019 0:00	F	1
119 Main Street		10/3/2019 0:00	F	0
100 Main Street		9/24/2019 0:00	G	0
106 Main Street		9/25/2019 0:00	G	5
105 Main Street		9/25/2019 0:00	G	4

<tbody>
</tbody>

 

[Peter_SSs]

The green pairs appear to use the previously stated tie-breaking rule. Not so for the orange pair. Can you clarify?

Excel Workbook
	A	B	C	D	E
1	Product	Auction End Date	Auction End Date 2	Status	Number of Photos
2	102 Main Street	9/26/2019 11:00			2
3	101 Main Street	9/26/2019 11:00			1
4	121 Main Street	9/27/2019 10:00			2
5	103 Main Street		9/27/2019 10:00	A	1
6	104 Main Street	9/27/2019 13:00			2
7	122 Main Street	9/28/2019 0:00			3
8	113 Main Street		9/29/2019 0:00	A	5
9	123 Main Street	9/29/2019 10:00			4
10	114 Main Street		9/30/2019 0:00	A	4
11	109 Main Street		9/27/2019 0:00	B	3
12	110 Main Street		9/28/2019 0:00	B	6
13	124 Main Street	10/01/2019 11:00			1
14	120 Main Street		10/04/2019 0:00	B	0
15	115 Main Street		10/01/2019 0:00	C	1
16	116 Main Street		10/01/2019 0:00	C	2
17	117 Main Street		10/01/2019 0:00	C	3
18	125 Main Street	10/01/2019 11:00			2
19	111 Main Street		9/29/2019 0:00	D	4
20	112 Main Street		9/29/2019 0:00	D	1
21	107 Main Street		9/26/2019 0:00	E	4
22	108 Main Street		9/27/2019 0:00	E	2
23	118 Main Street		10/02/2019 0:00	F	1
24	119 Main Street		10/03/2019 0:00	F	0
25	100 Main Street		9/24/2019 0:00	G	0
26	106 Main Street		9/25/2019 0:00	G	5
27	105 Main Street		9/25/2019 0:00	G	4
Sheet2

 

[vkorla]

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.

 

[Peter_SSs]

Oh gosh, I am sorry for this (again) Peter.

Does the fact that you are struggling to put them in the right order manually perhaps indicate this is a difficult task?

While we are on that subject, why are rows 13 and 18 not together?

 

[vkorla]

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?

 

[vkorla]

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.

 

[Peter_SSs]

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.

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
	A	B	C	D	E	F
1	Product	Auction End Date	Auction End Date 2	Status	Photos
2	106 Main Street		25/09/2019 0:00	G	5
3	124 Main Street	1/10/2019 11:00			1
4	116 Main Street		1/10/2019 0:00	C	2
5	120 Main Street		4/10/2019 0:00	B	0
6	123 Main Street	29/09/2019 10:00			4
7	113 Main Street		29/09/2019 0:00	A	5
8	107 Main Street		26/09/2019 0:00	E	4
9	102 Main Street	26/09/2019 11:00			2
10	125 Main Street	1/10/2019 11:00			2
11	119 Main Street		3/10/2019 0:00	F	0
12	109 Main Street		27/09/2019 0:00	B	3
13	100 Main Street		24/09/2019 0:00	G	0
14	114 Main Street		30/09/2019 0:00	A	4
15	121 Main Street	27/09/2019 10:00			2
16	112 Main Street		29/09/2019 0:00	D	1
17	118 Main Street		2/10/2019 0:00	F	1
18	111 Main Street		29/09/2019 0:00	D	4
19	115 Main Street		1/10/2019 0:00	C	1
20	104 Main Street	27/09/2019 13:00			2
21	105 Main Street		25/09/2019 0:00	G	4
22	108 Main Street		27/09/2019 0:00	E	2
23	101 Main Street	26/09/2019 11:00			1
24	122 Main Street	28/09/2019 0:00			3
25	103 Main Street		27/09/2019 10:00	A	1
26	117 Main Street		1/10/2019 0:00	C	3
27	110 Main Street		28/09/2019 0:00	B	6
Sheet2

.. and again after running the code above.:

Excel 2016
	A	B	C	D	E	F
1	Product	Auction End Date	Auction End Date 2	Status	Photos
2	102 Main Street	26/09/2019 11:00			2
3	101 Main Street	26/09/2019 11:00			1
4	121 Main Street	27/09/2019 10:00			2
5	103 Main Street		27/09/2019 10:00	A	1
6	104 Main Street	27/09/2019 13:00			2
7	122 Main Street	28/09/2019 0:00			3
8	113 Main Street		29/09/2019 0:00	A	5
9	123 Main Street	29/09/2019 10:00			4
10	114 Main Street		30/09/2019 0:00	A	4
11	109 Main Street		27/09/2019 0:00	B	3
12	110 Main Street		28/09/2019 0:00	B	6
13	125 Main Street	1/10/2019 11:00			2
14	124 Main Street	1/10/2019 11:00			1
15	120 Main Street		4/10/2019 0:00	B	0
16	117 Main Street		1/10/2019 0:00	C	3
17	116 Main Street		1/10/2019 0:00	C	2
18	115 Main Street		1/10/2019 0:00	C	1
19	111 Main Street		29/09/2019 0:00	D	4
20	112 Main Street		29/09/2019 0:00	D	1
21	107 Main Street		26/09/2019 0:00	E	4
22	108 Main Street		27/09/2019 0:00	E	2
23	118 Main Street		2/10/2019 0:00	F	1
24	119 Main Street		3/10/2019 0:00	F	0
25	100 Main Street		24/09/2019 0:00	G	0
26	106 Main Street		25/09/2019 0:00	G	5
27	105 Main Street		25/09/2019 0:00	G	4
Sheet2

 

[vkorla]

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?