Remove Duplicates from a cell/ Create a cell with unique values from a cell with duplicate Values.

[urubag]

Hello Guys,

Hope you can help,

Please note I am working on a big vba code and only this is part is missing or it is not working as expected not sure if the quantity of data is a problem.
Basically, I have a table with many lines with different columns that are being consolidated in one cell orange (image attached) and needs to be summarize in yellow cells per column.
Understood summarize is like when we do a pivot table and basically the pivot summarize the data removing duplicate values.

I need a VBA code to create a Function or vba macro in order can compile the information as unique values in yellow cells from orange cells (Basically I use a code to compile the data from the table into one cell)
The file attached have a sheet (Table) but the real data base uses more data and long sentences so unfortunately today I notice with 2000 lines it does not looks the data be summarized totally.

I was using this vba code found in google, but with 2000 lines, was not showing all the unique values supposed to have.

Function RemoveDupeWords(text As String, Optional delimiter As String = " ") As String
  Dim dictionary As Object
  Dim x, part

  Set dictionary = CreateObject("Scripting.Dictionary")
  dictionary.CompareMode = vbTextCompare
  For Each x In Split(text, delimiter)
    part = Trim(x)
    If part <> "" And Not dictionary.Exists(part) Then
      dictionary.Add part, Nothing
    End If
  Next

  If dictionary.Count > 0 Then
    RemoveDupeWords = Join(dictionary.keys, delimiter)
  Else
    RemoveDupeWords = ""
  End If

  Set dictionary = Nothing
End Function

MVP guys appreciate your support on this.
Regards
Andres

 

[eiloken]

Hello Guys,

Hope you can help,

Please note I am working on a big vba code and only this is part is missing or it is not working as expected not sure if the quantity of data is a problem.
Basically, I have a table with many lines with different columns that are being consolidated in one cell orange (image attached) and needs to be summarize in yellow cells per column.
Understood summarize is like when we do a pivot table and basically the pivot summarize the data removing duplicate values.

I need a VBA code to create a Function or vba macro in order can compile the information as unique values in yellow cells from orange cells (Basically I use a code to compile the data from the table into one cell)
The file attached have a sheet (Table) but the real data base uses more data and long sentences so unfortunately today I notice with 2000 lines it does not looks the data be summarized totally.

I was using this vba code found in google, but with 2000 lines, was not showing all the unique values supposed to have.

Function RemoveDupeWords(text As String, Optional delimiter As String = " ") As String
  Dim dictionary As Object
  Dim x, part

  Set dictionary = CreateObject("Scripting.Dictionary")
  dictionary.CompareMode = vbTextCompare
  For Each x In Split(text, delimiter)
    part = Trim(x)
    If part <> "" And Not dictionary.Exists(part) Then
      dictionary.Add part, Nothing
    End If
  Next

  If dictionary.Count > 0 Then
    RemoveDupeWords = Join(dictionary.keys, delimiter)
  Else
    RemoveDupeWords = ""
  End If

  Set dictionary = Nothing
End Function

MVP guys appreciate your support on this.
Regards
Andres

try this:

Function MergeDuplicate(ByVal orgTxt As String) As String
    Dim splTxt() As String
    Dim i As Long
    splTxt = Split(orgTxt, Chr(10))
    For i = LBound(splTxt) To UBound(splTxt)
        If MergeDuplicate <> Empty Then
            If InStr(MergeDuplicate, splTxt(i)) = 0 Then
                MergeDuplicate = MergeDuplicate & Chr(10) & splTxt(i)
            End If
        Else
            MergeDuplicate = splTxt(i)
        End If
    Next i
End Function