List of UNIQUE and NON-BLANK records

[Joris Hermans]

Hallo to you all.

Some time ago, I've posted a question on how to get a list of unique and non-blank values. (http://chandoo.org/forum/threads/get-list-of-unique-records.18254/)

Almost imedeatly after, I got a working answer .. and I'm still using the proposed solution (everytime with a lot of modification ...)
But now, I've still got a feeling, I do not exactly understand what's happening behind the scene...

So, is here someone who can explain to me in detail / step-by-step ... how this array formula works, this would be much appreciated.

"
(=IFERROR(INDEX(List;SMALL(IF(FREQUENCY(MATCH("~"&List;List&"";0);MATCH("~"&List;List&"";0));ROW(List)-5+1);ROWS(A$1:A1)));"")"


Ofcourse .. ANY ALTERNATIVE with the same outcome (a list of Unique and non-blank values, from another list/table) would be appreciated.

Kindest regards
(and happy summer holidays)

 

[Marc L]

Hi !

An advanced filter can create a list of unique, for non-blank
either with a filter before or adding a criteria to the advanced filter …
​

 

[NARAYANK991]

Hi Joris ,

There is a simpler formula , which is explained here :

http://www.get-digital-help.com/200...cally-create-a-unique-list-and-remove-blanks/

Using a Pivot Table is another option. After the Pivot Table is created , filter it for non-blank entries.

Narayan

 

[Joris Hermans]

Hello Narayan
thanks for the reply ... but this is not exactly the answer I was hoping for ..
I DO have some versions of the formula .. but I just need some (a lot?) of explanation how this ARRAY Formula works
For some reasons, I can not use a pivot table as source.

Maybe still someone can help me further on this?

Kindest regards

 

[NARAYANK991]

Hi ,

The link I posted shows a simpler formula , and also gives an explanation of how that simpler formula works.

If you want an explanation of a more difficult formula , then probably the person who provided this formula will come forward with an explanation.

Narayan