Extract a sorted and ranked unique list of items (help fix my formula)

[bruno_]

Hello,


My name is Bruno and it's my first time here.


I need a formula that will extract a sorted, unique list of strings, ranked by number of ocorrences (most ocurring items first). Example:


Original list (single column, unknown number os rows, with some blank cells):
CC
AA
(blank cell)
BB
(blank cell)
AA
CC
AA
(blank cell)
(blank cell)
(blank cell)


Resulting list would be (unique results, most frequent items first, no blank rows):
AA
CC
BB


The resulting list should update automatically when there are modifications in the original list, so using data pilot or filters is no good.


The following array formula is what I've got so far. It's entered on B2 and copied down:


=INDEX(lista;MODE(IF(ISNA(MATCH(list;B$1:B1;0));MATCH(list;list;0)*{1.1})))


It's a simplified (stripped down) version of Haseeb's formula, from the following post:
Ranking string data for one column « Chandoo.org - Learn Excel & Charting Online - Forums
Also mentioned in the following article:
Extract a Sorted & Ranked Unique list of items with criteria | Chandoo.org - Learn Microsoft Excel Online


Here is the problem: The formula works perfectly IF there are no blank cells in the original list (interval). A single blank cell turns all of the result cells into "#N/A". I've been trying to get it to work with blank cells (ignoring them) for several days, with no success. I use a lot of spreadsheets at work, but never used an array formula before.


Thanks in advance,
Bruno