Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Duplicate data row wise from each cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Duplicate data row wise from each cell
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 " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Duplicate data row wise from each cell
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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete duplicate data in a single cell | Excel Worksheet Functions | |||
delete duplicate data in the same cell | Excel Programming | |||
How to delete duplicate data | New Users to Excel | |||
Data row wise, formula column wise | Excel Discussion (Misc queries) | |||
Delete row wise duplicates & colomun wise simultaneously excel | Excel Worksheet Functions |