BlondieC
08-29-2017, 10:44 AM
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
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
Initial state of raw data:
Site CC
Site Name
Business Name
Revenue
101874
Toronto
Business 10
$10,150.22
103918
Montreal
Business 12
$999.81
15741
Vancouver
Business 5
$419.75
104144
Hamilton
Business 8
$553.60
438812
Paris
Business 23
$2,355.00
105250
Victoria
Business 54
$2,590.10
104982
Peterborough
Business 98
$2,473.55
356409
Point Pelee
Business 8
$0.00
104236
Prince Albert
Business 4
$429.30
Revenue Total
$19,971.33
After ranking:
Site CC
Site Name
Business Name
Ranking
101874
Toronto
Business 10
1
105250
Victoria
Business 54
2
104982
Peterborough
Business 98
3
438812
Paris
Business 23
4
103918
Montreal
Business 12
5
104144
Hamilton
Business 8
6
104236
Prince Albert
Business 4
7
15741
Vancouver
Business 5
8
356409
Point Pelee
Business 8
9
Revenue Total
$19,971.33
Desired outcome:
Site CC
Site Name
Business Name
Ranking
101874
Toronto
Business 10
1
105250
Victoria
Business 54
2
104982
Peterborough
Business 98
3
438812
Paris
Business 23
4
103918
Montreal
Business 12
5
104144
Hamilton
Business 8
6
104236
Prince Albert
Business 4
7
15741
Vancouver
Business 5
8
356409
Point Pelee
Business 8
$0.00
Revenue Total
$19,971.33
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
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
Initial state of raw data:
Site CC
Site Name
Business Name
Revenue
101874
Toronto
Business 10
$10,150.22
103918
Montreal
Business 12
$999.81
15741
Vancouver
Business 5
$419.75
104144
Hamilton
Business 8
$553.60
438812
Paris
Business 23
$2,355.00
105250
Victoria
Business 54
$2,590.10
104982
Peterborough
Business 98
$2,473.55
356409
Point Pelee
Business 8
$0.00
104236
Prince Albert
Business 4
$429.30
Revenue Total
$19,971.33
After ranking:
Site CC
Site Name
Business Name
Ranking
101874
Toronto
Business 10
1
105250
Victoria
Business 54
2
104982
Peterborough
Business 98
3
438812
Paris
Business 23
4
103918
Montreal
Business 12
5
104144
Hamilton
Business 8
6
104236
Prince Albert
Business 4
7
15741
Vancouver
Business 5
8
356409
Point Pelee
Business 8
9
Revenue Total
$19,971.33
Desired outcome:
Site CC
Site Name
Business Name
Ranking
101874
Toronto
Business 10
1
105250
Victoria
Business 54
2
104982
Peterborough
Business 98
3
438812
Paris
Business 23
4
103918
Montreal
Business 12
5
104144
Hamilton
Business 8
6
104236
Prince Albert
Business 4
7
15741
Vancouver
Business 5
8
356409
Point Pelee
Business 8
$0.00
Revenue Total
$19,971.33