How to remove duplicates across one row?

[DianaT]

Hello all.

I have an spreadsheet that looks similar to the below. I'm trying to build a formula for the last column, which counts the number of different names across each row but only counting duplicate once:

ID          Name 1        Name 2      Name 3        Name 4   Name 5       Number of name count

1            Kate             Marie                              Marie                          2

2            Fiona           Jo                Alex              Pam        Fiona           5

3            Fiona                                                   Fiona                          1

No success so far :( Any advice will be greatly appreciated. Thank you.

Cheers!

[#2 · Accepted solution]

Hi @DianaT 

Please see attached file which contains the function you are looking for in column G. Hope this helps

[#3]

Thank you so much @PascalKTeam. Works like a charm.

[#4]

Great! Happy to hear that!

Please mark this answer as the solutions

[#5]

Happy to do so! But has the forum changed recently? I can only mark "best response" (which I did) but no "mark as solution" button....

[#6]

"best response" that's the one. Thank you very much

[#7]

@DianaT 

The are some new approaches that are coming available in the new versions of Excel.  Given a list 'names', the formula

= FILTER( Names, ISTEXT(Names) )

will remove the blanks from the array.   The function UNIQUE may then be used to give an array of distinct names

= UNIQUE( FILTER( Names, ISTEXT(Names) ) )

I used a named formula 'distinctNames' for this, both the simplify the worksheet formula and to allow reuse.  The worksheet formula

= IF( COUNTA(Names), COUNTA( distinctNames ), "" )

only perform the count unique if names are present.