Using RANK on a range of data - need an exception

[BlondieC]

Hi, this is the last piece for a little tool I'm working on. I've used RANK for the data range but what I need is for any cell with 0.00 in Column E to remain with that cell content and not be ranked numerically and preferablyy be at the bottom of the data content.

Only cells >0.00 should be ranked highest to lowest based on the revenue dollar amount.

The idea is to have the ranking number displayed rather that the revenue dollar amount except where the revenue = 0.00. Currently the data is ranking the way it should and displays the way it should but the rows with 0.00 are also displaying a ranking number. Thank you

My code:

Sheets("SiteCopy").Select
    Range("E8:E" & Range("E7").End(xlDown).Offset(-1).Row).Name = "DataRank"
       
    With Range("DataRank").Offset(0, 1)
        .FormulaArray = "=RANK(DataRank,DataRank)"
    
End With

 

[Marcelo Branco]

I do not remember what I did
I have to start from scratch. Try to show us a new sample of data (where the current code fails), the expected results and the logic behind.
I have to leave now. I'll try to take a look later (tomorrow).
In the meantime maybe someone else can help.

M.

 

[BlondieC]

I know that feeling! Here is the code that works perfectly as long as there is more than one line to be ranked. I have comments next to two lines in the code.

Dim LastRow As Long    
    With Sheets("SiteCopy")
        LastRow = .Cells(8, "C").End(xlDown).Row
        .Range("E7") = "Ranking"
        With Range("F8:F" & LastRow)
            .Formula = "=IF(E8=0,E8,RANK(E8,E$8:E$" & LastRow & "))"   '*****Issue with this line: it ranks the one line of data 1 (which is correct) and then ranks the Revenue Total dollar amount directly below the business also as 1.  It also then results in a 0 being entered in all remaining cells in column F
            .Value = .Value
        End With
        .Range("B7:F" & LastRow).Sort key1:=.Range("E7"), order1:=xlDescending, Header:=xlYes
        .Range("F" & LastRow + 1) = .Range("E" & LastRow + 1)   '*******The code fails here when there is only one line of data or one business to rank and this is where I get the Runtime error 1004
        .Columns("E:E").Delete
        
        'Optional: formats last two cells
        .Range("E" & lastRow & ":E" & lastRow).NumberFormat = "[$$-409]#,##0.00"
    End With

This is the output produced - Because there is only one line of data it's ranking the total line also and then drops 0 into all remaining cells in column F:
[TABLE="width: 683"]
<tbody>[TR]
[TD]Site CC[/TD]
[TD]Site Name[/TD]
[TD]Business Name[/TD]
[TD]Ranking[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1038[/TD]
[TD]LOWER JAMES[/TD]
[TD]TORONTO[/TD]
[TD="align: right"]24309.25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Revenue Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24309.25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


These are the results when there is more than one line of data to rank and this is the desired and correct outcome:
[TABLE="width: 584"]
<tbody>[TR]
[TD]Site CC[/TD]
[TD]Site Name[/TD]
[TD]Business Name[/TD]
[TD]Ranking[/TD]
[/TR]
[TR]
[TD="align: right"]4812[/TD]
[TD]MEADOWLANDS[/TD]
[TD]TORONTO[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6867071[/TD]
[TD]ERIN[/TD]
[TD]EDMONTON[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Revenue Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$10,813.82[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

See if this fix it

Assumes data in Sheet SiteCopy columns A:D, headers in row 1, data beginning in row 2

Sub aTest()
    Dim lastRow As Long
    
    With Sheets("SiteCopy")
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range("E1") = "Ranking"
        With Range("E2:E" & lastRow)
            .Formula = "=IF(D2=0,D2,RANK(D2,D$2:D$" & lastRow & "))"
            .Value = .Value
        End With
        .Range("A1:E" & lastRow).Sort key1:=.Range("D1"), order1:=xlDescending, Header:=xlYes
        .Range("E" & lastRow + 1) = .Range("D" & lastRow + 1)
        .Columns("D:D").Delete
    End With
            
End Sub

M.

 

[BlondieC]

It's perfect! Thank you so much.

This is the new line of code that changed and what it was previously. I can see that in the new code it's counting up instead of down and refers to the column but not the row number this time. Although I can see the obvious change in the code I can't quite sort the logic out in my head to define what the new line of code does vs the previous line of code. Would you mind sharing with me an explanation so I can understand why it's now working.

lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row 'previous code: lastRow = .Cells(8, "C").End(xlDown).Row

 

[Marcelo Branco]

lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

This is like going to the last cell in column C; hit the End key followed by the Up-arrow key.
It takes the cursor to the last row with data in column C.
Try it.

M.

 

[Marcelo Branco]

My previous codes (posts 6 and 7) aren't correct (my bad )
lastRow = .Cells(2, "B").End(xlDown).Row

It should be
lastRow = .Cells(1, "B").End(xlDown).Row

that would generated the same result (provided there aren't blank cells in middle of the data in column B)

But i don't know why your code in post 10 is
LastRow = .Cells(8, "C").End(xlDown).Row
From where came this 8?

M.