# Extract a sorted and ranked unique list of items

1. ## Extract a sorted and ranked unique list of items

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:
http://chandoo.org/forums/topic/rank...for-one-column
Also mentioned in the following article:
http://chandoo.org/wp/2012/10/04/for...ensics-no-030/

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.

Bruno

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1