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]

Cool

 

[MrGNM]

I will plug in the actual data and let you know if there is anything that didn't work. Thank you so much! You have really helped me out a lot.

Regards,

Glenn

 

[cyrilbrd]

Please keep us posted, if you need additional help, just post again.

 

[MrGNM]

The two remaining items are these:

1. Can all of the rows be auto-sorted in order of rank (urgent down?)
2. If I have 50 rows of data, is this going to sort / rank all the way down to 50?

Thank you.

Glenn

 

[cyrilbrd]

yes it should work, if you sort the data per "final rank" it will place all "urgent" down the list, but this is only because they are text string, nonetheless the'll be down.

What do you mean by "auto-sorted"
Do you want the data to be sorted automatically by the "final rank"?

 

[MrGNM]

Yes, I was hoping the spreadsheet would autosort the rows by Final Rank each time the file was opened. Is this possible?

 

[MrGNM]

Also, I'd like to add a 5th rank to the first set (Column B in example) which is 'Lowest' and would like to know if any change is needed to your formula in column H of your spreadsheet. And, in order to begin populating the spreadsheet with actual data, what needs to be done with the fifteen cells of 'nodata ?' Can those be deleted or do they need to remain at the bottom of the spreadsheet at all times?

Thank you!

Glenn

 

[cyrilbrd]

Also, I'd like to add a 5th rank to the first set (Column B in example) which is 'Lowest'

Go to DB sheet and insert a row below row 4 "Low"
Call it "lowest" and type 5 in Cell B5.

Go to Insert / Name / Define...
This will open a floating window called "Define Name"
You should see there "rank1, rank2, value1, value2"

click on "rank1"
"Refers to" should show:

=DB!$A$1:$A$4

please change into:

=DB!$A$1:$A$5

and click on "Add"

then click on "value1"
"Refers to" should show:

=DB!$B$1:$B$4

please change into:

=DB!$B$1:$B$5

and click on "Add"
and click on "Ok"

Go back to Sheet1 and type in B2 the word "lowest" instead of "highest" and you'll see the value in F2 change to "5".

...if any change is needed to your formula in column H of your spreadsheet...

No, do not change the formula the named range is the most important part to update.

...what needs to be done with the fifteen cells of 'nodata ?...

You may replace them with actual data...

The formula in Column I will need to be extended. Right now the formula goes from Row 2 to Row 12.


You may extend it by changing the range in the formula bar or by dragging down the colored box in Column H, that box (highlighted cells) will appear when you click on the formula IN the formula bar, it shows the actual range if you will. So change the range accordingly.

To be specific if you want to add data till Row 30 the formula in I2 will change from:

=IF(B2="nodata","",RANK(H2, $H$2:$H$12,1))

to:

=IF(B2="nodata","",RANK(H2, $H$2:$H$30,1))

That is all you have to change.

...ells of 'nodata ?' Can those be deleted or do they need to remain at the bottom of the spreadsheet at all times?...

You may delete them, they are just for "cosmetic" the formula in Column H will return a blank cell if "nodata" is found in Column B or else it would return "100000000".

 

[MrGNM]

Go to DB sheet and insert a row below row 4 "Low"
Call it "lowest" and type 5 in Cell B5.

Go to Insert / Name / Define...
This will open a floating window called "Define Name"
You should see there "rank1, rank2, value1, value2"

click on "rank1"
"Refers to" should show:

=DB!$A$1:$A$4

please change into:

=DB!$A$1:$A$5

and click on "Add"

then click on "value1"
"Refers to" should show:

=DB!$B$1:$B$4

please change into:

=DB!$B$1:$B$5

and click on "Add"
and click on "Ok"

Go back to Sheet1 and type in B2 the word "lowest" instead of "highest" and you'll see the value in F2 change to "5".



No, do not change the formula the named range is the most important part to update.



You may replace them with actual data...

The formula in Column I will need to be extended. Right now the formula goes from Row 2 to Row 12.


You may extend it by changing the range in the formula bar or by dragging down the colored box in Column H, that box (highlighted cells) will appear when you click on the formula IN the formula bar, it shows the actual range if you will. So change the range accordingly.

To be specific if you want to add data till Row 30 the formula in I2 will change from:

=IF(B2="nodata","",RANK(H2, $H$2:$H$12,1))

to:

=IF(B2="nodata","",RANK(H2, $H$2:$H$30,1))

That is all you have to change.



You may delete them, they are just for "cosmetic" the formula in Column H will return a blank cell if "nodata" is found in Column B or else it would return "100000000".

HUGE thanks! Although you did not address my autosorting comment above the one you responded to. But I will do as you suggested and hope everything works. If I have any problems with this, I'll let you know. But otherwise. THANK YOU THANK YOU THANK YOU!

 

[cyrilbrd]

Cool!

Hope it works...

Cheers!