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
"JMB" wrote:
> 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
>
> "(E-Mail Removed)" wrote:
>
> > 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
> >
> >
Similar Threads
