Extract a sorted and ranked unique list of items
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):
Resulting list would be (unique results, most frequent items first, no blank rows):
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:
It's a simplified (stripped down) version of Haseeb's formula, from the following post:
Also mentioned in the following article:
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,
Last edited by JBeaucaire; 02-12-2013 at 03:29 AM.
Reason: Corrected thread title to topic only, as per forum rules
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1