Forum Discussion
How to remove duplicates across one row?
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!
Hi DianaT
Please see attached file which contains the function you are looking for in column G. Hope this helps
6 Replies
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.
- 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....
- "best response" that's the one. Thank you very much
Hi DianaT
Please see attached file which contains the function you are looking for in column G. Hope this helps
Thank you so much PascalKTeam. Works like a charm.
- Great! Happy to hear that!
Please mark this answer as the solutions
