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.

 

[MrGNM]

Will it autosort the rows?

 

[cyrilbrd]

This VBA will auto sort Sheet1 based on the values in Column J

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Worksheets("Sheet1").Range("j:j"), Target) Is Nothing) Then
        DoSort
    End If
End Sub

Private Sub DoSort()
    Worksheets("Sheet1").Range("A:j").Sort Key1:=Worksheets("Sheet1").Range("j1"), Order1:=xlAscending, Header:=xlYes
End Sub

Is this what you needed?

 

[MrGNM]

Yes, although I know nothing about VBA's so I don't know where / how to implement it.

 

[cyrilbrd]

Ok it a nutshell:

Copy the previously given code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left.
Choose Insert -Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

check this out, it has some pics that might help you out...
this link.

 

[MrGNM]

Thanks!

 

[MrGNM]

ISSUES I AM HAVING:

1. (Just FYI) _ I am using Excel 2010 so your instructions on how to Define Names were a little off, but I figured it out.

2. Deleting the cells 'nodata' returned #N/A in columns I and J FOR ALL ROWS.

3. In row 8 I entered (A) Name (B) Low (C) Major Task (D) 4/13/2012 (E) 5/1/12. The results generated: (F) 4 (G) 1 (H) 95100100 in blue (I) #N/A in blue (J) blank cell. Then in Row 9, cells F9:I9 all have #N/A in them.

I would be happy to e-mail you the file or send via dropbox.

 

[MrGNM]

4. Also receiving the following error message when attempting to add code to VBE as instructed: "The following features cannot be saved to macro-free workbooks: VB Project To save a file with these features, click No and then choose a macro-enabled fie type in the File Type list. To continue saving as a macro-free workbook, click Yes.

 

[cyrilbrd]

4. Also receiving the following error message when attempting to add code to VBE as instructed: "The following features cannot be saved to macro-free workbooks: VB Project To save a file with these features, click No and then choose a macro-enabled fie type in the File Type list. To continue saving as a macro-free workbook, click Yes.

Save file as (.xlsm) that is macro enabled.

The XML-based workbook format that preserves VBA and Excel 4.0 macro sheets.

 

[cyrilbrd]

2. Deleting the cells 'nodata' returned #N/A in columns I and J FOR ALL ROWS.

Change formula in Column H as follows:

=IFERROR(IF(ISBLANK(B2),"",(E2-TODAY())+(100-SUM(F2:G2))*10^6 + SUMPRODUCT(COUNTIF(F2:G2,{1,2,3,4,5,6})*10^{5,4,3,2,1,0})),"")

3. In row 8 I entered (A) Name (B) Low (C) Major Task (D) 4/13/2012 (E) 5/1/12. The results generated: (F) 4 (G) 1 (H) 95100100 in blue (I) #N/A in blue (J) blank cell. Then in Row 9, cells F9:I9 all have #N/A in them.

In F:

=IFERROR(IF(B2="nodata","",INDEX(value1,MATCH(B2,rank1,0))),"")

In G:

=IFERROR(IF(B2="nodata","",INDEX(value2,MATCH(C2,rank2,0))),"")

In I:

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

 

[MrGNM]

The changing of the formulas produced the values (F) 1 (G) 3 (H) 1 (I) 6 in Row 2. These values are aligned to the left and the cells are unhighlighted with heavy black borders.

I am not sure of how to copy the formulas to subsequent rows. Again, if there is some way to send this to you, I'd be happy to.

Thank you again. This must be getting frustrating.