Complex (to me) sorting / ranking problem

[MrGNM]

Hello - I suffer from ADHD and even with medication, I have a hard time thinking through complex problems like this. I'm hoping this is easier for someone than it has been for me. Thank you in advance for your assistance!

I would like to sort a list of items automatically and continuously. Each item and its associated information is listed in a row. We are taking a cumulative rank of these two columns and then further ranking by proximity to due date. If past due date, then ranking should be higher than those with due dates in the future. The spreadsheet should automatically sort / rank based on today's date.

The items themselves have the following criteria (each in a separate cell column): Name (irrelevant), A Value to which this formula / macro would assign a ranking of 1-4 (column uses actual words - Highest = 1, High = 2, Medium = 3, Low = 4), Values 1-5 (Also using names, same as previous), Today's Date, Due Date.

So each row would contain columns which look like this:

Name | Highest | Driver | 3/20/12 | 3/25/12 | RANK?
Name | Low | Milk | 3/19/12 | 3/18/12 | RANK?
Name | High | Blah | 3/20/12 | 6/03/12 | RANK?

I need the fifth column (In example above, "Rank?") to return a ranking for each row (1-3 in this example, but for every row in my spreadsheet) by taking the the rank of column 2 (1= Highest, 4= Lowest) plus column 3 (so that a rank of 1 in first column and 1 in second column produces the highest ranking), then the difference (number of actual days) between columns 4 and 5 as the third factor in the rank (so that days past due date is highest with lowest number of days to due date following.)

Please let me know what needs further clarification. Thanks again.

 

[cyrilbrd]

Hi,

I need the fifth column (In example above, "Rank?") to return a ranking for each row

that could be done with a simple formula.

I need the fifth column ... to return a ranking for each row ... by taking the the rank of column 2 ... plus column 3

Column 3 is "Driver,Milk and Blah" in your example, please clarify how is this correlated to the rank.


Kindly as well clarify how the date's difference act for the ranking.

Cheers!.

 

[MrGNM]

Hi Cyril,

Here is the actual form
-- removed inline image ---
at of the example:
(Sorry, I can't get this to line up properly and could not insert an image from my PC.)

1 (A) Name (B) Highest (C) Driver (D) 3/20/12 (E) 3/25/12 (F) RANK?
2 (A) Name (B) Low (C) Milk (D) 3/19/12 (E) 3/18/12 (F) RANK?
3 (A) Name (B) High (C) Blah (D) 3/20/12 (E) 6/03/12 (F) RANK?

In Column A, we have the the Item Name, which we are providing the automatic, continually updated ranking / priority to.

In column B, we have the words, "Highest, High, Medium, Low" which we could just convert to numbers 1-4 rather than assign them values, if that's easier.

In column C, we have the same situation (but different words) but 1-5 instead of 1-4.

Columns B and C are the "meat and potatoes." They provide the rank of importance of the Item in Column A.

We are then taking this rank and prioritizing it by the Date.

Column E represents the Date the Item was entered into the system and Column F represents the Due Date.

So now we want to assign a rank / priority to B/C according to it's proximity to the Due Date. If the Due Date is in the past (overdue) then that would boost the rank / priority.

I hope this is a better explanation and is helpful to you. I appreciate and am grateful for your assistance.

Glenn

 

[cyrilbrd]

Ok understood now.
No the words are fine, the formula can look for the equivalent integer.

How should the two (Column B and Column C) ranks add up? will it be an "average"? or is one more important than the other? To what ratio?

Then for the "boost" if the due date is overdue, will it be a boost equivalent to the number of days? "3 days due so +3"? or would it be a coefficient? "add 10% per day".

Sorry for this, just trying to understand fully the concept, also this will help other find the most appropriate answer.

 

[MrGNM]

Ok understood now.
No the words are fine, the formula can look for the equivalent integer.

How should the two (Column B and Column C) ranks add up? will it be an "average"? or is one more important than the other? To what ratio?

Then for the "boost" if the due date is overdue, will it be a boost equivalent to the number of days? "3 days due so +3"? or would it be a coefficient? "add 10% per day".

Sorry for this, just trying to understand fully the concept, also this will help other find the most appropriate answer.

Don't apologize because I'm not providing enough information. These are excellent questions.

For the two columns B/C, I don't have the specific answer, but a 1-1, 2-1, 3-1 should all rank higher than 2-4 on down. Hope this make sense.

For the date boost, if the Due Date is in the past, it should automatically rank above anything with a date in the future and ranked in order of # days past DD. This will trigger either removal of item or change of due date.

THANK YOU!

Glenn

 

[cyrilbrd]

Ok understood

For the date boost, if the Due Date is in the past, it should automatically rank above anything with a date in the future and ranked in order of # days past DD. This will trigger either removal of item or change of due date.

I would as well suggest for a conditional formatting to highlight in a different color all rows with past "due date".

How long usually will it take for you to either remove the item or adjust the date? Mean to say I am considering what ratio to use for the boost. 1 day delay will be less than 2 days delayed and so on, so my question is how many could it be "delayed" maximum?

 

[cyrilbrd]

Hi Glenn,
Would it look like this?

	Rank 1	Rank 2	Entry Date	Due Date	hide	hide	hide	hide	Final Rank
Name	Highest	name3	3/20/12      	4/13/12	1	3	96101000	3	2
Name	Low	name1	3/19/12      	4/14/12	4	1	95100100	4	4
Name	High	name1	3/20/12      	6/3/12	2	1	97110000	1	1
Name	Highest	name2	3/19/12      	4/10/12	1	2	97110000	1	URGENT
Name	low	name2	3/19/12      	4/15/12	4	2	94010100	5	5
Name	medium	name3	3/19/12      	4/16/12	3	3	94002000	6	6
nodata	nodata	nodata	nodata	nodata					
nodata	nodata	nodata	nodata	nodata					
nodata	nodata	nodata	nodata	nodata

Please let me know.
You can as well have a go at the linked file for you to see if it fits your needs.
I'll wait for your feedback for further modifications.

Link to your file.

 

[MrGNM]

Again, thanks for your assistance. You've definitely done it. We just need to tweak it a little.

It would seem this is configured properly although I'm trying to understand why Row 4 with 1/2 and due date out in June is returning a Rank of 1, above Row 2 with a 1/3 and DD of 4/13. Seems it should be the opposite. Also, there's no Final Rank of 2. 1, Urgent, 3-6.

 

[cyrilbrd]

Yes, That is why I wanted to suggest for the following:

	Rank 1	Rank 2	Entry Date	Due Date	hide	hide	hide	hide	Final Rank
Name	Highest	name3	3/20/12      	4/16/12	1	3	96101000	4	4
Name	Highest	name3	3/19/12      	4/14/12	1	3	96100998	1	URGENT
Name	Highest	name3	3/20/12      	6/3/12	1	3	96101048	6	6
Name	Highest	name3	3/19/12      	4/19/12	1	3	96101003	5	5
Name	Highest	name3	3/19/12      	4/15/12	1	3	96100999	3	2
Name	Highest	name3	3/19/12      	4/14/12	1	3	96100998	1	URGENT
nodata	nodata	nodata	nodata	nodata					
nodata	nodata	nodata	nodata	nodata					
nodata	nodata	nodata	nodata	nodata

In this proposal, the past due dates are flagged as "URGENT", then the closer dates are ranked in order of priority "1" being urgent and "6" being set in the future. I intentionally here gave the same ranking for all names for you to see how the ranking will vary with the due date.

Is this more like what you are looking for?

link to the new proposal.

 

[MrGNM]

This looks correct to me.