Custom sort order

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!

ProductDateStatusNumber of PhotosStatus RankOverall Rank
A9/26/19 10:0032
B9/26/19 10:0051
C9/27/19 10:0043
D9/28/19 10:0034
E9/29/19L237
F9/30/19M159
G10/1/19N1610
H10/1/19O348
I10/3/19P026
J10/3/19Q115
K9/24/19R0711

<tbody>
</tbody>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
ABCF
1ProductDateStatusHelper
2A26/09/2019 10:0043734.41667
3B26/09/2019 10:0043734.41667
4C27/09/2019 10:0043735.41667
5D28/09/2019 10:0043736.41667
6E29/09/2019L1000003
7F30/09/2019M1000005
8G1/10/2019N1000006
9H1/10/2019O1000004
10I3/10/2019P1000002
11J3/10/2019Q1000001
12K24/09/2019R1000007
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
ABCF
1ProductDateStatusHelper
2A26/09/2019 10:0043734.41667
3B26/09/2019 10:0043734.41667
4C27/09/2019 10:0043735.41667
5D28/09/2019 10:0043736.41667
6J3/10/2019Q1000001
7I3/10/2019P1000002
8E29/09/2019L1000003
9H1/10/2019O1000004
10F30/09/2019M1000005
11G1/10/2019N1000006
12K24/09/2019R1000007
Sort Order (2)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
BCDF
1DateStatusPhotosHelper
226/09/2019 1:00143734.54167
325/09/2019 13:00043734.54167
Sort Order (4)



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

Excel Workbook
BCDF
1DateStatusPhotosHelper
226/09/2019 1:00143734.0416676667
325/09/2019 13:00043733.5416676667
427/09/2019 10:00443735.4166676088
528/09/2019 10:00343736.4166676088
63/10/2019Q11000001.0000010000
73/10/2019P01000002.0000010000
829/09/2019L21000003.0000010000
91/10/2019O31000004.0000010000
1030/09/2019M11000005.0000010000
111/10/2019N11000006.0000010000
1224/09/2019R01000007.0000010000
Sort Order (5)
 
Upvote 0
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?
 
Upvote 0
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
ABCDEFG
1ProductAuction End DateAuction End Date 2StatusNumber of PhotosForeclosure RankOverall Rank
2aa27/09/2019 10:0052
3bb27/09/2019 10:0033
4cc28/09/2019 10:0044
5dd28/09/2019 10:0035
6ee27/09/2019 0:00Q226
7ff26/09/2019 0:00Q211
8gg3/10/2019 0:00Q348
9hh3/10/2019 0:00Q137
10ii29/09/2019 0:00P2610
11jj1/10/2019 0:00P359
12kk30/09/2019 0:00L1711
13ll1/10/2019 0:00O1812
14mm26/09/2019 0:00M21115
15nn26/09/2019 0:00M51014
16oo24/09/2019 0:00M0913
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
ABCDEFG
1ProductAuction End DateAuction End Date 2StatusNumber of PhotosForeclosure RankOverall Rank
2ff26/09/2019 0:00Q211
3aa27/09/2019 10:0052
4bb27/09/2019 10:0033
5cc28/09/2019 10:0044
6dd28/09/2019 10:0035
7ee27/09/2019 0:00Q226
8hh3/10/2019 0:00Q137
9gg3/10/2019 0:00Q348
10jj1/10/2019 0:00P359
11ii29/09/2019 0:00P2610
12kk30/09/2019 0:00L1711
13ll1/10/2019 0:00O1812
14oo24/09/2019 0:00M0913
15nn26/09/2019 0:00M51014
16mm26/09/2019 0:00M21115
Sheet1 (2)
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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