Find duplicates within a single Cell

[jamieostrich]

I have a table with multiple values in the same cell separated by commas.
I also have duplicate values within the cell.

54 6,88,198,205,415,6,205 55 <tbody> </tbody> 6,88,198,205,415,527,1432,1446,1462,6,1432 <tbody> </tbody>

<tbody>
</tbody>

Basically I want it so that a number can only appear once in the comma separated list.
So for cell B1, the second 6 and 205 would be removed.
And for Cell B2, the second 6 and 1432 would be removed.

Any help would be much appreciated, many thanks

 

[hiker95]

Re: How to Find duplicates within a single Cell

jamieostrich,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample raw data:

Excel 2007
	A	B
1	54	6,88,198,205,415,6,205
2	55	6,88,198,205,415,527,1432,1446,1462,6,1432
3
Sheet1

After the macro:

Excel 2007
	A	B
1	54	6,88,198,205,415
2	55	6,88,198,205,415,527,1432,1446,1462
3
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub RemoveDupes()
' hiker95, 09/12/2014, ME804994
Dim rng As Range, c As Range, s, i As Long
Application.ScreenUpdating = False
Set rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
With CreateObject("Scripting.Dictionary")
  For Each c In rng
    If c <> "" Then
      If InStr(c, ",") Then
        s = Split(Trim(c), ",")
        For i = LBound(s) To UBound(s)
          If Not .Exists(s(i)) Then
            .Add s(i), s(i)
          End If
        Next i
        c.Value = Join(.Keys, ",")
      End If
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Then run the RemoveDupes macro.

 

[hiker95]

Re: How to Find duplicates within a single Cell

jamieostrich,

If you have a problem, where you receive a message concerning the Microsoft Scripting Runtime?


You may have to add the Microsoft Scripting Runtime to the References - VBA Project.

With your workbook that contains the above:

Press the keys ALT + F11 to open the Visual Basic Editor

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveDupes macro.

 

[samonedome]

Re: How to Find duplicates within a single Cell

Hi guys,

I have a similar issue regarding duplicates in cells, however the cell data is different.

To elaborate:

I have imported data which includes addresses.

In column C these addresses are listed for each company.

I want to split the address data, however many of them have duplicates in the cell which distorts the amount of columns needed hugely.

Many have duplicates i.e. "7 Green Avenue, London, London, WC1 A12" - this should show as "7 Green Avenue, London, WC1 A12'

What macro would I need for this? Bear in mind that I am also using Excel on a Mac.

 

[hiker95]

Re: How to Find duplicates within a single Cell

samonedome,

Welcome to the MrExcel forum.

Bear in mind that I am also using Excel on a Mac.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else, with Mac experience, will assist you.

 

[raogm2001]

Re: How to Find duplicates within a single Cell

Hi,
I have seen your post dated Apr 2009, removing duplicates from a single cell,
I have range D6 to J75, each cell contains names separated by comma and space, and some times repeated for example D6 as Joes, Tony, Samuel, Joes, and E8 as Adrian, Sam, Prakash, Tony, After the macro: It should highlight Joes in D6 & Tony in E8,


Any help would be much appreciated, many thanks

jamieostrich,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample raw data:

Excel 2007

	A	B
1	54	6,88,198,205,415,6,205
2	55	6,88,198,205,415,527,1432,1446,1462,6,1432
3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

After the macro:

Excel 2007

	A	B
1	54	6,88,198,205,415
2	55	6,88,198,205,415,527,1432,1446,1462
3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub RemoveDupes()
' hiker95, 09/12/2014, ME804994
Dim rng As Range, c As Range, s, i As Long
Application.ScreenUpdating = False
Set rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
With CreateObject("Scripting.Dictionary")
  For Each c In rng
    If c <> "" Then
      If InStr(c, ",") Then
        s = Split(Trim(c), ",")
        For i = LBound(s) To UBound(s)
          If Not .Exists(s(i)) Then
            .Add s(i), s(i)
          End If
        Next i
        c.Value = Join(.Keys, ",")
      End If
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Then run the RemoveDupes macro.

 

[hiker95]

Re: How to Find duplicates within a single Cell

raogm2001,

So that I can get it right on the first try, can I see your actual raw data workbook/worksheet, and, on another worksheet what the results (manually formatted by you) should look like?


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com