Forum Discussion

PeteVapors's avatar
PeteVapors
Copper Contributor
Aug 24, 2023
Solved

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

 

 

Excel on Mac
  • HansVogelaar's avatar
    HansVogelaar
    Aug 26, 2023

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

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 26, 2023

    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.

  • PeteVapors's avatar
    PeteVapors
    Copper Contributor
    Aug 26, 2023
    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))
  • PeteVapors's avatar
    PeteVapors
    Copper Contributor
    Aug 25, 2023
    Hi Detlef, thanks for reply. I'm a novice at Excel and have no knowledge of the functions you've suggested.
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 24, 2023

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

     

     

    • PeteVapors's avatar
      PeteVapors
      Copper Contributor
      Aug 25, 2023
      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.
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Aug 25, 2023

        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.

Resources