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]

A data sample along with expected results would be helpful.

M.

 

[BlondieC]

Initial state of raw data:

[TABLE="width: 542"]
<tbody>[TR]
[TD]Site CC[/TD]
[TD]Site Name[/TD]
[TD]Business Name[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]101874[/TD]
[TD]Toronto[/TD]
[TD]Business 10[/TD]
[TD="align: right"]$10,150.22[/TD]
[/TR]
[TR]
[TD]103918[/TD]
[TD]Montreal[/TD]
[TD]Business 12[/TD]
[TD="align: right"]$999.81[/TD]
[/TR]
[TR]
[TD]15741[/TD]
[TD]Vancouver[/TD]
[TD]Business 5[/TD]
[TD="align: right"]$419.75[/TD]
[/TR]
[TR]
[TD]104144[/TD]
[TD]Hamilton[/TD]
[TD]Business 8[/TD]
[TD="align: right"]$553.60[/TD]
[/TR]
[TR]
[TD]438812[/TD]
[TD]Paris[/TD]
[TD]Business 23[/TD]
[TD="align: right"]$2,355.00[/TD]
[/TR]
[TR]
[TD]105250[/TD]
[TD]Victoria[/TD]
[TD]Business 54[/TD]
[TD="align: right"]$2,590.10[/TD]
[/TR]
[TR]
[TD]104982[/TD]
[TD]Peterborough[/TD]
[TD]Business 98[/TD]
[TD="align: right"]$2,473.55[/TD]
[/TR]
[TR]
[TD]356409[/TD]
[TD]Point Pelee[/TD]
[TD]Business 8[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]104236[/TD]
[TD]Prince Albert[/TD]
[TD]Business 4[/TD]
[TD="align: right"]$429.30[/TD]
[/TR]
[TR]
[TD]Revenue Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$19,971.33[/TD]
[/TR]
</tbody>[/TABLE]


After ranking:

[TABLE="width: 542"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Site CC[/TD]
[TD]Site Name[/TD]
[TD]Business Name[/TD]
[TD]Ranking[/TD]
[/TR]
[TR]
[TD]101874[/TD]
[TD]Toronto[/TD]
[TD]Business 10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]105250[/TD]
[TD]Victoria[/TD]
[TD]Business 54[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]104982[/TD]
[TD]Peterborough[/TD]
[TD]Business 98[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]438812[/TD]
[TD]Paris[/TD]
[TD]Business 23[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]103918[/TD]
[TD]Montreal[/TD]
[TD]Business 12[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]104144[/TD]
[TD]Hamilton[/TD]
[TD]Business 8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]104236[/TD]
[TD]Prince Albert[/TD]
[TD]Business 4[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]15741[/TD]
[TD]Vancouver[/TD]
[TD]Business 5[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]356409[/TD]
[TD]Point Pelee[/TD]
[TD]Business 8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Revenue Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$19,971.33[/TD]
[/TR]
</tbody>[/TABLE]

Desired outcome:

[TABLE="width: 542"]
<tbody>[TR]
[TD]Site CC[/TD]
[TD]Site Name[/TD]
[TD]Business Name[/TD]
[TD]Ranking[/TD]
[/TR]
[TR]
[TD]101874[/TD]
[TD]Toronto[/TD]
[TD]Business 10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]105250[/TD]
[TD]Victoria[/TD]
[TD]Business 54[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]104982[/TD]
[TD]Peterborough[/TD]
[TD]Business 98[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]438812[/TD]
[TD]Paris[/TD]
[TD]Business 23[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]103918[/TD]
[TD]Montreal[/TD]
[TD]Business 12[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]104144[/TD]
[TD]Hamilton[/TD]
[TD]Business 8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]104236[/TD]
[TD]Prince Albert[/TD]
[TD]Business 4[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]15741[/TD]
[TD]Vancouver[/TD]
[TD]Business 5[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]356409[/TD]
[TD]Point Pelee[/TD]
[TD]Business 8[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]Revenue Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$19,971.33[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Do you really want to delete the column Revenue?

M.

 

[BlondieC]

In this case yes as this worksheet will then be created as a .pdf and can be distributed to the various businesses without disclosing the business revenues. Another worksheet in the workbook will retain the site revenue for the Regional Manager to have on hand - this being a pivot table from which I've essentially copied and pasted values only onto the SiteCopy worksheet using some additional code and tweaked a few things. It is the data on the SiteCopy worksheet that I am wanting to rank. The Regional Managers want to create a bit of a competitive environment by showing businesses within their territory how they are ranked.

I did at one time try using the following formula in the worksheet - placed in column F: =IF(E8=0, "0.00", RANK(E8, $E$8:$E$36, 0)). However, the end of the range will change with each Manager and I wasn't able to get it to copy down to the last cell based on that being the second last cell in Column E with revenue data - as the total revenue amount is not to be included.

 

[Marcelo Branco]

See if this example helps

Adjust sheet name, ranges, etc to suit your case

Before macro
Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Site CC​

[/TD]
[TD]

Site Name​

[/TD]
[TD]

Business Name​

[/TD]
[TD]

Revenue​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

101874​

[/TD]
[TD]

Toronto​

[/TD]
[TD]

Business 10​

[/TD]
[TD]

$10.150,22​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

103918​

[/TD]
[TD]

Montreal​

[/TD]
[TD]

Business 12​

[/TD]
[TD]

$999,81​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

15741​

[/TD]
[TD]

Vancouver​

[/TD]
[TD]

Business 5​

[/TD]
[TD]

$419,75​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

104144​

[/TD]
[TD]

Hamilton​

[/TD]
[TD]

Business 8​

[/TD]
[TD]

$553,60​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

438812​

[/TD]
[TD]

Paris​

[/TD]
[TD]

Business 23​

[/TD]
[TD]

$2.355,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

105250​

[/TD]
[TD]

Victoria​

[/TD]
[TD]

Business 54​

[/TD]
[TD]

$2.590,10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

104982​

[/TD]
[TD]

Peterborough​

[/TD]
[TD]

Business 98​

[/TD]
[TD]

$2.473,55​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

356409​

[/TD]
[TD]

Point Pelee​

[/TD]
[TD]

Business 8​

[/TD]
[TD]

$0,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

104236​

[/TD]
[TD]

Prince Albert​

[/TD]
[TD]

Business 4​

[/TD]
[TD]

$429,30​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Revenue Total​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

$19.971,33​

[/TD]
[/TR]
</tbody>[/TABLE]


Macro

Sub aTest()
    Dim lastRow As Long
    
    With Sheets("Sheet1")
        lastRow = .Cells(2, "B").End(xlDown).Row
        .Range("E1") = "Ranking"
        With Range("E2:E" & lastRow)
            .Formula = "=IF(D2=0,D2,RANK(D2,D$2:D$" & lastRow & "))"
            .Value = .Value
        End With
        With .Range("A1:E" & lastRow)
            .Sort key1:=.Range("D1"), order1:=xlDescending, Header:=xlYes
        End With
        .Range("E" & lastRow + 1) = .Range("D" & lastRow + 1)
        .Columns("D:D").Delete
        
        'Optional: formats last two cells
        .Range("D" & lastRow & ":D" & lastRow + 1).NumberFormat = "[$$-409]#,##0.00"
    End With
            
End Sub

After macro

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Site CC​

[/TD]
[TD]

Site Name​

[/TD]
[TD]

Business Name​

[/TD]
[TD]

Ranking​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

101874​

[/TD]
[TD]

Toronto​

[/TD]
[TD]

Business 10​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

105250​

[/TD]
[TD]

Victoria​

[/TD]
[TD]

Business 54​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

104982​

[/TD]
[TD]

Peterborough​

[/TD]
[TD]

Business 98​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

438812​

[/TD]
[TD]

Paris​

[/TD]
[TD]

Business 23​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

103918​

[/TD]
[TD]

Montreal​

[/TD]
[TD]

Business 12​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

104144​

[/TD]
[TD]

Hamilton​

[/TD]
[TD]

Business 8​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

104236​

[/TD]
[TD]

Prince Albert​

[/TD]
[TD]

Business 4​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

15741​

[/TD]
[TD]

Vancouver​

[/TD]
[TD]

Business 5​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

356409​

[/TD]
[TD]

Point Pelee​

[/TD]
[TD]

Business 8​

[/TD]
[TD]

$0,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Revenue Total​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

$19.971,33​

[/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps

M.

 

[Marcelo Branco]

Small adjustment in code

try

Sub aTest()
    Dim lastRow As Long
    
    With Sheets("Sheet1")
        lastRow = .Cells(2, "B").End(xlDown).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
        
        'Optional: formats last two cells
        .Range("D" & lastRow & ":D" & lastRow + 1).NumberFormat = "[$$-409]#,##0.00"
    End With
            
End Sub

M.

 

[BlondieC]

Absolutely beautiful! I adjusted the sheet name and columns and rows to reflect my worksheet and it's perfect. I stepped through it one line at a time and it's much cleaner and more efficient than what I had coded. Some of what you incorporated I had coded separately. It makes more sense to include it the way you did. And I like how you did the ranking and retaining that data. I always try throughout the year to look for opportunities to develop new tools or enhance my existing portfolio and often find it's great in my head but extends beyond my current knowledge. Lucky for me my desire to develop small tools like this and my curiosity lead me to the path of learning on a daily basis. Thank you for your help with this!

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[BlondieC]

Everything was working as desired and then I was asked to make some changes to address some privacy concerns. I made the changes and now have one glitch. Prior to making the requested changes there would always be more than one line of data or business to be ranked so everything worked fine. Now in some cases there is only one line of data or one business. If it were me and there was only one line I wouldn't see any reason to do any ranking etc. However, I have 100+ end users and one of them is bound to click the button that sets the ranking etc in motion and then the code fails. The code failing results in the error message: Runtime error '1004' Application-defined or object-defined error.

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
        .Columns("E:E").Delete
        
        'Optional: formats last two cells
        .Range("E" & lastRow & ":E" & lastRow).NumberFormat = "[$$-409]#,##0.00"
    End With