Excel - List of Unique Names?

[PeteVapors]

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

[#2]

@PeteVapors 

Use UNIQUE() or a data model pivot table.

[#3]

@PeteVapors 

One of these perhaps.

List = your range

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

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

[#4]

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.

[#5]

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

[#6]

Are all your names in 1 cell?

[#7]

@PeteVapors 

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.

[#8]

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

[#9]

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

[#10 · Accepted solution]

@PeteVapors 

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))

[#11]

@PeteVapors 

In general that could be

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

if only it's in the same column C. 

[#12]

Hi Hans,

Thanks very much for your reply and suggestions.

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

[#13]

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))

[#14]

@PeteVapors 

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.

[#15]

@PeteVapors 

I would go with @SergeiBaklan'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.