SOLVED

# Excel - List of Unique Names?

Copper Contributor

# Excel - List of Unique Names?

Hi,

I'm a relative novice with Excel.

I have a column of hundreds of names - many of which are duplicated. Both the first name and surname are in the same cell in one long column.

Is there a function or combination of functions which could be entered into a cell below this list which would identify how many unique names there are in the whole column?

A simple number in the cell is all that's required.

thanks for help

Pete

PS - I'm using Excel for Mac

14 Replies

# Re: Excel - List of Unique Names?

Use UNIQUE() or a data model pivot table.

# Re: Excel - List of Unique Names?

One of these perhaps.

``````'Count after dupes are removed.
=COUNTA(UNIQUE(list))

'Count of names only appearing once in list.
=COUNTA(UNIQUE(list,,1))``````

# Re: Excel - List of Unique Names?

Hi Patrick,

Your first example is applicable to what I want but upon use it only gives a value of 1 instead of the total number of unique names. Anything else needed.

# Re: Excel - List of Unique Names?

Hi Detlef, thanks for reply. I'm a novice at Excel and have no knowledge of the functions you've suggested.

# Re: Excel - List of Unique Names?

Are all your names in 1 cell?

# Re: Excel - List of Unique Names?

That is odd.  The danger with the formula is typically one of giving 'false positives' where trivial differences give rise to distinct names.  To get 1 it is likely that the COUNTA is hiding a problem with FILTER.  It would be worth seeing what you get from

``= UNIQUE(list)``

before performing the count.  My best guess is that you are using an obsolete version of Excel that does not have the UNIQUE function.

# Re: Excel - List of Unique Names?

Hi Patrick

The complete names of each person are in their own cells - so 638 cells in total. However, some of those cells have no name in. Is that a factor?
Thanks, pPete

# Re: Excel - List of Unique Names?

Hi Peter,
I'll try your suggestion. The Excel version I'm using is 2019 for Mac. Some of the cells I've listed (C4:C641) are blanks - is this a factor? If so, any way of excluding them?
Thanks, Pete
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: Excel - List of Unique Names?

Does this formula work for you? It doesn't use functions that are only available in recent versions of Excel.

=SUMPRODUCT((C4:C641<>"")/COUNTIF(C4:C641,""&C4:C641))

or (confirmed by pressing Control+Shift+Return or Command+Return):

=SUM(IF(FREQUENCY(IF(C4:C641<>"",MATCH(C4:C641,C4:C641,0)),ROW(C4:C641)-ROW(C4)+1),1))

# Re: Excel - List of Unique Names?

In general that could be

``=ROWS( UNIQUE( TOCOL(C:C,3) ) )``

if only it's in the same column C.

# Re: Excel - List of Unique Names?

Hi Hans,

I've used the first version you've supplied and it has returned a figure of 417 unique names. By adding "-1" to this formula to remove the blank cell I've arrived at the correct number required.

Thank you very much for taking the time to produce this, it's very much appreciated.

Kind Regards

Pete

# Re: Excel - List of Unique Names?

Thanks to everyone who has replied to my original question. Great to have input from everyone. Problem has been resolved by Hans in one of the replies

=SUMPRODUCT((C4:C641<>"")/COUNTIF(C4:C641,""&C4:C641))

# Re: Excel - List of Unique Names?

The formula already excludes blank cells from the count!

But if you have cells that only contain a space (or multiple spaces), those would be included.

# Re: Excel - List of Unique Names?

I would go with @Sergei Baklan's formula because I can.  The hard-wired range C:C could be replaced by a 2D range (or even 3D range across sheets) and it still works.  It will ignore both blank cells and errors.

The catch is that Excel 2019 was already obsolete when it was first issued since dynamic arrays, which came out in 2018, didn't make the cut.

1 best response

Accepted Solutions
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: Excel - List of Unique Names?

Does this formula work for you? It doesn't use functions that are only available in recent versions of Excel.

=SUMPRODUCT((C4:C641<>"")/COUNTIF(C4:C641,""&C4:C641))

or (confirmed by pressing Control+Shift+Return or Command+Return):

=SUM(IF(FREQUENCY(IF(C4:C641<>"",MATCH(C4:C641,C4:C641,0)),ROW(C4:C641)-ROW(C4)+1),1))