Combine unique cell values on a single row

See below image of my 3 inputs (columns A:C) and expected out put (column D),

I would like to list the unique values for each row as an output, separated by a comma.

Solution 1:

In cell D1 use :

=TRIM(A1&IF(COUNTIF(A1:C1,B1)>1,"",","&B1)&IF(COUNTIF(A1:C1,C1)>1,"",","&C1))

Then drag down.

If you have more columns, then add &IF(COUNTIF(A1:C1,C1)>1,"",","&C1) change the C1 to D1 for a fourth column and to E1 for a fifth, and so on.

Also change A1:C1 in the COUNTIF to suit the range of however many columns you may be using.

Solution 2:

If you have Office 365 Excel then you can use TEXTJOIN as an array formula:

=TEXTJOIN(",",TRUE,IF(COLUMN(A2:C2)=MATCH(A2:C2,A2:C2,0),A2:C2,""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

This iterates and test if the instance is the first and if so it will add it to the string.

Solution 3:

This simple UDF will help you to combine unique values in cell separated by Comma.

• Insert this code as Module with the related Sheet.

    Function CombineUnique(xRg As Range, xChar As String) As String
  
    Dim xCell As Range
    Dim xDic As Object
    Set xDic = CreateObject("Scripting.Dictionary")
    For Each xCell In xRg
        xDic(xCell.Value) = Empty
    Next
    CombineUnique = Join$(xDic.Keys, xChar)
    Set xDic = Nothing
    End Function  
  

How it works:

• Before Run this UDF Add Microsoft Scripting Runtime using Tools, References in VB Editor window.

• To get desire result, in Cell D2 write below
  written Formula and fill down.

  =CombineUnique(A2:C2,",")

Note, adjust the cell references in Formula as needed.