VBA Compare two cells (comma separated data) and return unique values

[Moebyus]

Hello,

my data will look like this.

R7404, R7405, R7406, R7600, R7606, R7607, R7608, R7611, R7645, R7803, R7810, R7817, R7822, R7825, R7827, R7832, R7833, R7834, R7835, R7837

R2102, R2128, R7404, R7405, R7406, R7600, R7606, R7607, R7608, R7611, R7645, R7803, R7810, R7817, R7822, R7825, R7827, R7832, R7833, R7834, R7835, R7837

Rows (around 100) that contains two cells and each cell will have multiple values separated by comma and space. I need to compare those two cell and find out which ones are unique. In this case the result would be like this (same format)

R2102, R2128

I was able to do it through formulas

=TEXTJOIN(", ";TRUE;UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ";TRUE;A1;B1);;", "));TRUE;TRUE))

I could rewrite this in VBA, but maybe it could be done differently because it will be used in larger macro that I'm trying to build.

Thank you!

 

[Fluff]

You could use

=TEXTJOIN(", ",,UNIQUE(TEXTSPLIT(A2&", "&B2,,", "),,1))

 

[Georgiboy]

Here is one way to get that into VBA:

Sub test()
    Dim Rng1 As String, Rng2 As String, rStr As String
    
    Rng1 = Range("A1").Address(, , , 1)
    Rng2 = Range("B1").Address(, , , 1)

    rStr = Evaluate("TEXTJOIN("", "",,UNIQUE(TEXTSPLIT(" & Rng1 & "&"", ""&" & Rng2 & ",,"", ""),,1))")
End Sub

 

[Legacy 143009]

Hi,

I don't guarantee performance:

Sub test()
  MsgBox getUniqueValues(Range("A1"), Range("B1"))
End Sub
Function getUniqueValues(c1 As Range, c2 As Range) As String
  Dim myArray1 As Variant, myArray2 As Variant
  myArray1 = Split(c1.Value, ", ")
  myArray2 = Split(c2.Value, ", ")
  For i = LBound(myArray1) To UBound(myArray1)
    For j = LBound(myArray2) To UBound(myArray2)
      If myArray1(i) = myArray2(j) And myArray1(i) <> "" Then
        myArray1(i) = ""
        myArray2(j) = ""
      End If
    Next
  Next
  For Each elm In myArray1
    If elm <> "" Then
      getUniqueValues = getUniqueValues & ", " & elm
    End If
  Next
  For Each elm In myArray2
    If elm <> "" Then
      getUniqueValues = getUniqueValues & ", " & elm
    End If
  Next
  getUniqueValues = Right(getUniqueValues, Len(getUniqueValues) - 2)
End Function