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]

Yes, it was not checked. It made 18 replacements, but the dates that were left aligned are still left aligned and the same error persists when running the code

 

[Peter_SSs]

Yes, it was not checked. It made 18 replacements, but the dates that were left aligned are still left aligned and the same error persists when running the code

I don't know what file you are working with. That sample file file you provided has 26 cells in columns B:C containing an "M", not 18.

Whatever file you are working with, did you ensure that columns B:C were formatted as Date/Time (like the sample file is) before you did the Find/Replace?

I did the Find/Replace on the sample file & ran the code and the sorting was done without error (apart from that the dates are in the wrong format for my regional settings ). Try the process on that sample file.

 

[vkorla]

This is really strange... even when I download the sample file I shared with you, when I select cells B2:C27 and do a find/replace, it only does 18 replacements (you're absolutely right in that there are 26 instances of "M"), so I have no idea what's going on... is there a way you could share your file with me if you wouldn't mind?

 

[Peter_SSs]

I really don't have a file of mine at the moment. I just downloaded yours, did the find/replace, ran the code & closed without saving.

Try find/replace with / instead of M and see if you get 52 replacements, or 36 or some different number. Also check that 'No Format Set' appears beside both the Find & Replace boxes.
If not 52 then Undo and try on smaller ranges until you can track down which cells are finding M (or /) and which are not. That might turn something up.

Another option that would normally convert those text values into dates is to just select 1 of the columns (eg C2:C27) then do Text to Columns (Data ribbon tab) -> Delimited -> Next -> Remove any check marks from delimiters (just to be safe) -> Finish
Then do the same with column B

 

[vkorla]

Thanks so much for all your help Peter! The issue with the dates was that in some cells I had input them as DD/MM/YYYY and in others it was MM/DD/YYYY. Thanks again & wish you all the best!

 

[Peter_SSs]

You're very welcome. It was an interesting exercise. Glad you resolved the issue about the dates.