Delete Duplicate data row wise from each cell

[tarone]

I have a big file and I want to delete duplicate data in rows
separated by comma in same cell

i.e

Sample File

COL A
Pepsi Cola, Coke, Pepsi Cola, Toyota Mercedes, M700, Toyota, Tom,
Peter, Coke
Pepsi Cola
Pepsi Cola, Coke, Coke
Tom, Peter, Mike, Don, Paul, Tom, Mike
Pepsi Cola, Coke, Pepsi Cola, Toyota Mercedes, Toyota
M700, M700
There are like 10,000 rows like this
.....
...


Required File
Pepsi Cola, Coke, Toyota Mercedes, M700, Toyota, Tom, Peter
Pepsi Cola
Pepsi Cola, Coke
Tom, Peter, Mike, Don, Paul
Pepsi Cola, Coke, Toyota Mercedes
M700
There are like 10,000 rows like this
.....
...


Required: We just want to delete the duplicate data ROW WISE from same
cell

Any solution will be appreciated

Regards

 

[JMB]

You could try a UDF. If your data is in A1 and your delimiter is a comma,
the syntax is

=rmvDupes(A1, ",")

more here if you are new to VBA:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Function RmvDupes(varData As String, _
strDelimiter As String) As String
Dim lngPos1 As Long

On Error GoTo ExitFunction
varData = Trim(varData)
If Right(varData, 1) <> strDelimiter And _
Len(varData) > 0 Then _
varData = varData & strDelimiter
lngPos1 = InStr(1, varData, strDelimiter, vbTextCompare)
If lngPos1 > 0 Then
RmvDupes = Left(varData, lngPos1) & " " & _
Replace(RmvDupes(Trim(Right(varData, _
Len(varData) - lngPos1)), strDelimiter), _
Left(varData, lngPos1), "", 1, -1, vbTextCompare)
If Right(Trim(RmvDupes), 1) = strDelimiter Then _
RmvDupes = Left(Trim(RmvDupes), _
Len(Trim(RmvDupes)) - 1)
RmvDupes = Application.Trim(RmvDupes)
End If

ExitFunction:

End Function

 

[JMB]

some corrections, I had hardcoded the length of the delimiter (using 1).
this should work better.

Function RmvDupes(varData As String, _
strDelimiter As String) As String
Dim lngPos1 As Long

On Error GoTo ExitFunction
varData = Trim(varData)
If Right(varData, Len(strDelimiter)) <> _
strDelimiter And Len(varData) > 0 Then _
varData = varData & strDelimiter
lngPos1 = InStr(1, varData, strDelimiter, vbTextCompare)
If lngPos1 > 0 Then
RmvDupes = Left(varData, lngPos1 + _
Len(strDelimiter) - 1) & " " & _
Replace(RmvDupes(Trim(Right(varData, _
Len(varData) - lngPos1 - _
Len(strDelimiter) + 1)), strDelimiter), _
Left(varData, lngPos1 + Len(strDelimiter) - 1), _
"", 1, -1, vbTextCompare)
If Right(Trim(RmvDupes), _
Len(strDelimiter)) = strDelimiter Then _
RmvDupes = Left(Trim(RmvDupes), _
Len(Trim(RmvDupes)) - Len(strDelimiter))
RmvDupes = Application.Trim(RmvDupes)
End If

ExitFunction:

End Function