excel - Remove duplicates from combined data in one cell

Question

I'm looking to remove duplicates in one cell where the data is combined from 2 cells. For example :

cell 1 : ABC & DEF
cell 2 : ABC, DEF & LMN

I want both cells 1 & 2 data to combine, which will remove the duplicates at the same time and appear to be : ABC, DEF & LMN in cell 3.

Appreciate if someone can help me on this. Thank you in advance.

***Correction - Sorry if I didn't input the details well. The data are actually names, for example :

Cell 1 : John Mayer & Britney Spears Cell 2 : John Mayer, Britney Spears & Selena Gomez

The outcome I want in Cell 3 : John Mayer, Britney Spears & Selena Gomez

Meaning that there are spaces in between the words. The commas and ampersand is very important as well.*

标签： excelexcel-formula

Answer 1

在公式出现之前，这可能会有所帮助。将其复制粘贴到模块中：

Public Function GetString(STR1 As String, STR2 As String) As String

Dim ARR1() As String, ARR2() As String, ARR3() As String
Dim X As Long, Y As Long, POS As Long

STR1 = Replace(STR1, " & ", ",")
STR1 = Replace(STR1, ", ", ",")
STR2 = Replace(STR2, " & ", ",")
STR2 = Replace(STR2, ", ", ",")

ARR1() = Split(STR1, ",")
ARR2() = Split(STR2, ",")

Y = 0
For X = LBound(ARR1) To UBound(ARR1)
    ReDim Preserve ARR3(Y)
    ARR3(Y) = ARR1(X)
    Y = Y + 1
Next X

For X = LBound(ARR2) To UBound(ARR2)
    ReDim Preserve ARR3(Y)
    ARR3(Y) = ARR2(X)
    Y = Y + 1
Next X

For X = LBound(ARR3) To UBound(ARR3)
    POS = InStr(GetString, ARR3(X))
    If POS <> 0 Then
        If Mid(GetString, POS + Len(ARR3(X)), 1) <> "," Then
            GetString = GetString & ARR3(X) & ", "
        End If
    Else
        GetString = GetString & ARR3(X) & ", "
    End If
Next X

GetString = Left(GetString, Len(GetString) - 2)
GetString = StrReverse(Replace(StrReverse(GetString), StrReverse(","), StrReverse(" &"), , 1))

End Function

像这样称呼它：

=GetString(Cell1, Cell2)

结果：