Find duplicates within a single Cell

J

jamieostrich

New Member
Joined
Sep 12, 2014
Messages
1
I have a table with multiple values in the same cell separated by commas.
I also have duplicate values within the cell.

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, align: right"] [TABLE="width: 500"]
<tbody>[TR]
[TD]54[/TD]
[TD]6,88,198,205,415,6,205[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD]55[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6,88,198,205,415,527,1432,1446,1462,6,1432[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
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
AB
1546,88,198,205,415,6,205
2556,88,198,205,415,527,1432,1446,1462,6,1432
3
Sheet1


After the macro:


Excel 2007
AB
1546,88,198,205,415
2556,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.

Code: 
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 to expand...

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.
 
Upvote 0
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


hiker95 said:
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
AB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]54[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]6,88,198,205,415,6,205[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]6,88,198,205,415,527,1432,1446,1462,6,1432[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



After the macro:

Excel 2007
AB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]54[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]6,88,198,205,415[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]6,88,198,205,415,527,1432,1446,1462[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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.

Code: 
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.
Click to expand...
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA Macro to type a formula in a cell throws Type missmatch error
Replies
3
Views
95
ChrisMd
C
C
  • Solved
VBA - Checking for Duplicates
Replies
2
Views
981
cwhaley1
C
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
484
shg
shg
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
549
MARZIOTULLIO
M
D
  • Solved
remove duplicate if cell appears twice macro
2
Replies
15
Views
989
daveyc18
D

Forum statistics

Threads
1,226,218
Messages
6,189,693
Members
453,563
Latest member
Aswathimsanil

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top