Finding unique names

Question

I need to find a list of  the unique names in a column containing many entries of the same name.   Many years ago, Lotus 123 had a function called RUNIQUE which listed one occurrence of each 'name' from a column of multiple names.   As I don't know what the names are, I can't use COUNTIF etc.

Answer 1

Why don't you use Remove duplicates. 

Just in case, Copy the column to a new column with the same set of data. Select this column alone and click data --> Remove duplicates to get list of unique values.

Answer 2

Hi,

If you want to pull out the unique names to a new column you can use this array formula, see below for how to enter it. ARRAY enter the formula in cell E2 and drag down

=IFERROR(INDEX($A$1:$A$20,MATCH(0,IF(ISBLANK($A$1:$A$20),"",COUNTIF(E$1:$E1,$A$1:$A$20)),0)),"")

Note if you put the formula into a different cell then the bold bit in the formula must refer to the cell above where you put it.

This is an array formula which must be entered by pressing CTRL+Shift+Enter

and not just Enter. If you do it correctly then Excel will put curly brackets

around the formula {}. You can't type these yourself. If you edit the formula

you must enter it again with CTRL+Shift+Enter.

Answer 3

Hi,

You may refer to my solution at this link - http://www.ashishmathur.com/dynamically-extract-unique-values-with-no-conditions/.

Hope this helps.

Answer 4

Thanks to both respondent's - each solution seems to provide the right result.