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]

 

[Peter_SSs]

It isn't entirely clear to me.
1. Why are the first 2 rows ranked 2, 1 when their dates/times are identical? Could they just as well be ranked 1, 2?

2. You didn't say whether you are looking for a manual way to do this, a formula method or a macro.

Anyway, see if this manual method is any use. Note that my dates are in d/m/y format

Copy the formula shown down

Excel Workbook
	A	B	C	F
1	Product	Date	Status	Helper
2	A	26/09/2019 10:00		43734.41667
3	B	26/09/2019 10:00		43734.41667
4	C	27/09/2019 10:00		43735.41667
5	D	28/09/2019 10:00		43736.41667
6	E	29/09/2019	L	1000003
7	F	30/09/2019	M	1000005
8	G	1/10/2019	N	1000006
9	H	1/10/2019	O	1000004
10	I	3/10/2019	P	1000002
11	J	3/10/2019	Q	1000001
12	K	24/09/2019	R	1000007
Sort Order (2)

Now select all the data and sort smallest to largest based on that formula column. The result for me is:

Excel Workbook
	A	B	C	F
1	Product	Date	Status	Helper
2	A	26/09/2019 10:00		43734.41667
3	B	26/09/2019 10:00		43734.41667
4	C	27/09/2019 10:00		43735.41667
5	D	28/09/2019 10:00		43736.41667
6	J	3/10/2019	Q	1000001
7	I	3/10/2019	P	1000002
8	E	29/09/2019	L	1000003
9	H	1/10/2019	O	1000004
10	F	30/09/2019	M	1000005
11	G	1/10/2019	N	1000006
12	K	24/09/2019	R	1000007
Sort Order (2)

 

[vkorla]

Thanks so much, Peter. I should have clarified... the first 2 rows are ranked 2, 1 because one of them has more photos than the other (even though they have the same date/time), so I'd like for the one with more photos to appear before the one with less photos.

In terms of whether to use a macro or formula, I'd say whatever is most efficient!

Thanks again.

 

[Peter_SSs]

If you are happy to use a formula and then sort manually then just add this to the end of the previous formula

=IF(C2="",B2,10^6+FIND(C2,"QPLOMNR"))+1/(D2+1)

We could build this into a macro. If you want that, can you confirm what columns contain data that that need to be included when the sort is done. Is it just columns A:D?

 

[vkorla]

Thanks Peter. Yes, exactly, columns A:D for the final output is perfect.

Can you please help me understand how adding "+1/(D2+1)" results in this formula sorting by the number of photos?

 

[Peter_SSs]

Thanks Peter. Yes, exactly, columns A:D for the final output is perfect.

Can you please help me understand how adding "+1/(D2+1)" results in this formula sorting by the number of photos?

The previous formula produced a number that allowed sorting based on the date/time or status.
The blue bits adds a further fraction to that number. The more pictures there are the smaller the fraction added (since the number of pictures is in the denominator of the fraction). Since the lower numbers get sorted to the top, the more pictures there are the further towards the top the row is likely to be sorted. The +1 at the end of the denominator is to avoid division by zero errors if there are no photos

However, in responding to your question, I have realised that my formula may not produce the correct result. For example, row 3 below has an earlier date/time than row 2 but because of the photo fraction additions the Helper column ends up the same so sorting does not produce the correct result.

Excel Workbook
	B	C	D	F
1	Date	Status	Photos	Helper
2	26/09/2019 1:00		1	43734.54167
3	25/09/2019 13:00		0	43734.54167
Sort Order (4)

I think the solution is to ensure the photo fraction is much smaller, so ...

Excel Workbook
	B	C	D	F
1	Date	Status	Photos	Helper
2	26/09/2019 1:00		1	43734.0416676667
3	25/09/2019 13:00		0	43733.5416676667
4	27/09/2019 10:00		4	43735.4166676088
5	28/09/2019 10:00		3	43736.4166676088
6	3/10/2019	Q	1	1000001.0000010000
7	3/10/2019	P	0	1000002.0000010000
8	29/09/2019	L	2	1000003.0000010000
9	1/10/2019	O	3	1000004.0000010000
10	30/09/2019	M	1	1000005.0000010000
11	1/10/2019	N	1	1000006.0000010000
12	24/09/2019	R	0	1000007.0000010000
Sort Order (5)

 

[vkorla]

Beast mode, thanks a tonne Peter, you've been so helpful!

 

[Peter_SSs]

Beast mode, thanks a tonne Peter, you've been so helpful!

You're welcome.

 

[vkorla]

Hi Peter, would you be able to help with a more nuanced sort? The data file is here.

This is a list of products going to auction. I would like to sort all the products by either the "Auction End Date" or "Auction End Date 2" columns in ascending order so that the products going to auction sooner are listed before those going to auction later. However, for a product that has a value in the "Auction End Date 2" column, these products must first be sorted in order of a particular status: Q, P, L, O, M, and then in ascending order of the "Auction End Date 2" value. 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.

Ultimately, the final sort should then be as described in column G, "Overall Rank". Is this possible via a macro or formula?

 

[Peter_SSs]

Always best to show your sample data here if you can. Some potential helpers will not bother to look elsewhere. Hence I've reproduced your sample data here.

Excel 2016
	A	B	C	D	E	F	G
1	Product	Auction End Date	Auction End Date 2	Status	Number of Photos	Foreclosure Rank	Overall Rank
2	aa	27/09/2019 10:00			5		2
3	bb	27/09/2019 10:00			3		3
4	cc	28/09/2019 10:00			4		4
5	dd	28/09/2019 10:00			3		5
6	ee		27/09/2019 0:00	Q	2	2	6
7	ff		26/09/2019 0:00	Q	2	1	1
8	gg		3/10/2019 0:00	Q	3	4	8
9	hh		3/10/2019 0:00	Q	1	3	7
10	ii		29/09/2019 0:00	P	2	6	10
11	jj		1/10/2019 0:00	P	3	5	9
12	kk		30/09/2019 0:00	L	1	7	11
13	ll		1/10/2019 0:00	O	1	8	12
14	mm		26/09/2019 0:00	M	2	11	15
15	nn		26/09/2019 0:00	M	5	10	14
16	oo		24/09/2019 0:00	M	0	9	13
Sheet1

Having sorted the data on column G (below) I am not understanding the required logic this time. If
26 September with status Q comes before 27 September with no status, why doesn't
27 September with status Q come before 28 September with no status?

Excel 2016
	A	B	C	D	E	F	G
1	Product	Auction End Date	Auction End Date 2	Status	Number of Photos	Foreclosure Rank	Overall Rank
2	ff		26/09/2019 0:00	Q	2	1	1
3	aa	27/09/2019 10:00			5		2
4	bb	27/09/2019 10:00			3		3
5	cc	28/09/2019 10:00			4		4
6	dd	28/09/2019 10:00			3		5
7	ee		27/09/2019 0:00	Q	2	2	6
8	hh		3/10/2019 0:00	Q	1	3	7
9	gg		3/10/2019 0:00	Q	3	4	8
10	jj		1/10/2019 0:00	P	3	5	9
11	ii		29/09/2019 0:00	P	2	6	10
12	kk		30/09/2019 0:00	L	1	7	11
13	ll		1/10/2019 0:00	O	1	8	12
14	oo		24/09/2019 0:00	M	0	9	13
15	nn		26/09/2019 0:00	M	5	10	14
16	mm		26/09/2019 0:00	M	2	11	15
Sheet1 (2)