I was asked this week if it was possible to delete duplicated values inside the same cell, since the the function:
DATA / REMOVE DUPLICATES functionality was only working for a selection of columns / range etc.
In a couple of minutes, you will be able to create a macro that will be able to search into a range if duplicated values are showing in a single cell and remove it.
If, for example, in one single cell we have the values:
Steve, Marc, Alex, Carla, Marc, Carla, Paul
Once the macro run, it will convert to:
Steve, Marc, Alex, Carla, Paul
This is because Marc and Carla are showing twice.
Let's start with the process for the macro.
The first step is to select the column with duplicated values. In my example , Let's say that we have multiple same value in same cells in Column
A and rows
Once we know the target column, we are now able to start the macro.
ALT + F11 keys opens the Microsoft Visual Basic for Applications window.
Insert menu, click
Copy the below code into the code window of the module:
Sub Remove_Duplicates()Dim firstvalue As String Dim lastvalue As String Dim arraystr() As String Dim x As Long Dim k As Long Dim cell As Range Dim rw As LongApplication.ScreenUpdating = False ' for each cells in the range For Each cell In Sheets("Sheet1").Range("A2:A19") Erase arraystr ' erase array lastvalue = "" ' erase final value" firstvalue = cell.Value On Error Resume Next arraystr = Split(firstvalue, ",") 'Go into the full string and find duplication For rw = 0 To UBound(arraystr) For k = rw + 1 To UBound(arraystr) If Trim(arraystr(k)) = Trim(arraystr(rw)) Then arraystr(k) = "" 'if this is a duplication, delete the array value End If Next k Next rw ' combine all the values without the duplicates For x = 0 To UBound(arraystr) If arraystr(x) <> "" Then lastvalue = lastvalue & Trim(arraystr(x)) & ", " End If Next x ' Then delete the last empty space plus the extra comma lastvalue = Trim(lastvalue) lastvalue = Left(lastvalue, Len(lastvalue) - 1) cell.Offset(0, 0).Value = lastvalue Next cell 'AutoSize the column A an the end Columns("A:A").Select Selection.EntireColumn.AutoFitApplication.ScreenUpdating = TrueEnd Sub
2. You should parameterize your code so that it receives a range parameter rather than using hard coded ranges. Although it is not an in-place substitution, you can see an example of parameterized user-defined function in my Better Concatenation Function article.
Alternatively, your code can prompt the user to select a range. In Excel, you have the ability to use the Application.Inputbox method with a data type of Range (8).
3. Like the range, you might allow the user to supply the delimiter.
4. If you had used a comma-space ", " delimiter rather than just the comma delimiter "," , you could eliminate the need to trim your split results. Any trimming actions could also be a parameterized/prompted.
Open in new windowProduces the following results in the Immediate window:
Open in new window
If you used a dictionary object, you can do this:
Open in new windowWhere rngCell refers to the current cell in your range iteration and dicUnique is a dictionary object variable with unique names.