|
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com