Forum Discussion
Solved
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 d...
Hi DianaT
Please see attached file which contains the function you are looking for in column G. Hope this helps
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.