Remove duplicates in same cell

[VBquery757]

Using Excel 2016, I need to accomplish the following without the use of Macro or Visual Basic.
I have a column that contains the following examples obtained from an Index formula. This is the Index formula I am currently using to return these values.
=INDEX('ProjectX ABC'!B5:L42,2,6)

3.1, 3.2, 3.3
3.1
3.1, 3.2, 3.1, 3.2
3.2, 3.2, 3.1, 3.3, 3.1
3.3, 3.1, 3.3, 3.2
etc.

I need it to only return/keep one occurrence for each row.
3.1, 3.2, 3.3
3.1
3.1, 3.2
3.2, 3.1, 3.3
3.3, 3.1, 3.2

each of these rows is associated with an individual employee who worked on these respective tasks.

Thanks in advance for your help.
VBquery757

 

[eiloken]

i create this custom function to remove duplicates in string:

Option Explicit

Function RemoveDuplicatesInCell(ByVal rng As Range, ByVal determiner As String) As Variant
    Dim cll As Range
    Dim splArr() As String, rejointarr() As Variant
    Dim splColl As Collection
    Dim i As Long, j As Long, k As Long
    Dim issatified As Boolean
    Dim istring As String
    Dim myarr() As Variant
    ReDim myarr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
    For Each cll In rng
        If Not IsEmpty(cll) And Not IsError(cll) Then
            splArr = Split(cll.Text, determiner)
            Set splColl = New Collection
            For i = LBound(splArr) To UBound(splArr)
                issatified = True
                If splColl.Count > 0 Then
                    For j = 1 To splColl.Count
                        If splColl(j) = splArr(i) Then issatified = False
                    Next j
                End If
                If issatified Then splColl.Add splArr(i)
            Next i
            ReDim rejointarr(splColl.Count - 1)
            For k = 1 To splColl.Count
                rejointarr(k - 1) = splColl(k)
            Next k
            myarr(cll.Row - rng.Cells(1).Row + 1, cll.Column - rng.Cells(1).Column + 1) = WorksheetFunction.TextJoin(determiner, False, rejointarr)
        End If
    Next cll
    RemoveDuplicatesInCell = myarr
End Function

in your example:

=RemoveDuplicatesInCell("your range here",", ")

 

[VBquery757]

i create this custom function to remove duplicates in string:

Option Explicit

Function RemoveDuplicatesInCell(ByVal rng As Range, ByVal determiner As String) As Variant
    Dim cll As Range
    Dim splArr() As String, rejointarr() As Variant
    Dim splColl As Collection
    Dim i As Long, j As Long, k As Long
    Dim issatified As Boolean
    Dim istring As String
    Dim myarr() As Variant
    ReDim myarr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
    For Each cll In rng
        If Not IsEmpty(cll) And Not IsError(cll) Then
            splArr = Split(cll.Text, determiner)
            Set splColl = New Collection
            For i = LBound(splArr) To UBound(splArr)
                issatified = True
                If splColl.Count > 0 Then
                    For j = 1 To splColl.Count
                        If splColl(j) = splArr(i) Then issatified = False
                    Next j
                End If
                If issatified Then splColl.Add splArr(i)
            Next i
            ReDim rejointarr(splColl.Count - 1)
            For k = 1 To splColl.Count
                rejointarr(k - 1) = splColl(k)
            Next k
            myarr(cll.Row - rng.Cells(1).Row + 1, cll.Column - rng.Cells(1).Column + 1) = WorksheetFunction.TextJoin(determiner, False, rejointarr)
        End If
    Next cll
    RemoveDuplicatesInCell = myarr
End Function

in your example:

=RemoveDuplicatesInCell("your range here",", ")

Thank you for the code. I appreciate it, however, my company does not allow the use of Macros. I will need to accomplish this with a formula not generated by Visual Basic if any combination of formulas even exists. Thanks again for your help!