VBA for comparing values in two columns and removing duplicate information

[Mike_Gr]

Hi!
I'm trying to figure out a smart way of comparing text strings in two columns and returning the values that are unique for column B in column C. The order of the values is random and superfluous commas and spaces need to be removed (no commas occur within the individual values).
All help is greatly appreciated!


[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]chocolate, strawberry
[/TD]
[TD]banana, chocolate, strawberry
[/TD]
[TD]banana
[/TD]
[/TR]
[TR]
[TD]vanilla, blueberry
[/TD]
[TD]licorice, raspberry, vanilla, fudge, blueberry
[/TD]
[TD]licorice, raspberry, fudge
[/TD]
[/TR]
</tbody>[/TABLE]

 

[nemmi69]

Assuming that your data started in A1 to B2. Formula goes in C2. Remember to use shift-ctrl-enter. You can now drag down C2 to fill remaining rows.

=INDIRECT(TEXT(MIN(IF(($A$2:$B$3<>"")*(COUNTIF($C$1:C1,$A$2:$B$3)=0),ROW($2:$3)*100+COLUMN($A:$B),7^8)),"R0C00"),)&""

 

[nemmi69]

Sorry this is not what you wanted. I will keep looking

 

[nemmi69]

I can do this via VBa not formula at the moment.

 

[Mike_Gr]

Thanks a lot for looking into this.

 

[nemmi69]

Got to say this one has me stumped! I found a supposed way to do it with TEXTJOIN but we do not have that available (good old Microsoft not issuing an update to existing 2016 users). Any other Excel guru's can help?

 

[Fluff]

How about

Function MikeGr(St1 As String, St2 As String) As String
   Dim Sp As Variant
   Dim i As Long
   Sp = Split(St2, ",")
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For i = 0 To UBound(Sp)
         If Not Trim(Sp(i)) = "" Then .Item(Trim(Sp(i))) = Empty
      Next i
      Sp = Split(St1, ",")
      For i = 0 To UBound(Sp)
         If .Exists(Trim(Sp(i))) Then .Remove Trim(Sp(i))
      Next i
      MikeGr = Join(.keys, ", ")
   End With
End Function

Used like =MikeGr(A2,B2)

 

[nemmi69]

How about

Function MikeGr(St1 As String, St2 As String) As String
   Dim Sp As Variant
   Dim i As Long
   Sp = Split(St2, ",")
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For i = 0 To UBound(Sp)
         If Not Trim(Sp(i)) = "" Then .Item(Trim(Sp(i))) = Empty
      Next i
      Sp = Split(St1, ",")
      For i = 0 To UBound(Sp)
         If .Exists(Trim(Sp(i))) Then .Remove Trim(Sp(i))
      Next i
      MikeGr = Join(.keys, ", ")
   End With
End Function

Used like =MikeGr(A2,B2)

The aim is to do it via formula. I can do a single value searching a comma separated cell but not a coma separated cell searching another.

 

[Fluff]

The aim is to do it via formula.

Where do you get that from?
The title clearly states VBA

 

[nemmi69]

Where do you get that from?
The title clearly states VBA

Gaaah, my bad! Could have sworn that a formula was wanted. I could have done the VBa ages ago. Must admit it is quite the challenge to do it via a formula and keep it to one cell.