2 part ranking question

[pgarcia]

Hello all,
I have a list that changes daily. The data is a 3 digit station code, e.g.
LAX, GGV, JFX, ect. First, I need to rank the station by occurrence, e.g. if
LAX comes up 15x and GGV comes up 5x then LAX would be ranked 1 and GGV 2,
ect. Then, rank the station to the division it finds it's self, e.g. LAX is
Western, GGV Western, JFX Eastern, ect. (there are only three division,
Western, Southern and Eastern).

Thanks

 

[Chip Pearson]

You can get ranks by occurrences with a set of formulas like the following.
You'll need the DistinctValues VBA function available at
http://www.cpearson.com/Excel/DistinctValues.aspx.

Suppose your data is in A1:A10. In B1, enter
=DistinctValues($A$1:$A$10)
and fill down to B10.

In C1, enter =IF(B1="","",COUNTIF($A$1:$A$10,B1))
and fill down to C10.

In D1, enter
=IF(B1="","",OFFSET($B$1,IF(C1="","",COUNT($C$1:$C$10)-(RANK(C1,$C$1:$C$10)+COUNTIF($C$1:C1,C1)-1)+1)-1,0,1,1))
and fill down to D10.

This will create in D110 a list of the distinct items from A1:A10 ordered
by frequency in ascending order (least common value to most common value).
If you want the list in the reverse order, enter the following array formula
in E1 and fill down to E10.

=IF(D1="","",OFFSET($D$1,MAX((--($D$1:$D$10<>""))*(--(ROW($D$1:$D$10))))-ROW(),0))

This is an array formula, so you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula in the formula bar
enclosed in curly braces { }. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for more information about
array formulas.

Ranking data is covered in much more depth at
http://www.cpearson.com/Excel/rank.aspx.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)