Solved

Remove duplicate within cell.

Posted on 2013-01-14
6
372 Views
Last Modified: 2013-01-14
Hello all.

I have a data set with eleven columns. The last column contains a set of codes separated by commas - sample '1, 2, 3'.

In some cases I have duplicate codes '1, 2, 2, 3'.

I need to remove the duplicate code... result '1, 2, 3'.

Suggestions?
0
Comment
Question by:model_un
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 38775044
this vba may work

Sub Remove_DupesInString()
     ' this puts the final value in column J
    Dim starval As String
    Dim finval As String
    Dim strarray() As String
    Dim x As Long
    Dim k As Long
     ' step through each cell in range
    For Each cell In Sheets(1).Range("A1:A500")
        Erase strarray ' erase array
        finval = "" ' erase final value"
        starval = cell.Value
         
        strarray = Split(starval, ",")
         
         'Step through length of string and look for duplicate
        For rw = 0 To UBound(strarray)
             
            For k = rw + 1 To UBound(strarray)
                If Trim(strarray(k)) = Trim(strarray(rw)) Then
                    strarray(k) = "" 'if duplicate clear array value
                End If
            Next k
        Next rw
         
         ' combine all value in string less duplicate
        For x = 0 To UBound(strarray)
            If strarray(x) <> "" Then
                 
                finval = finval & Trim(strarray(x)) & ", " 
            End If
             
        Next x
         ' remove last space and comma
        finval = Trim(finval)
        finval = Left(finval, Len(finval) - 1)
         ' output value to Column J
        cell.Offset(0, 9).Value = finval
         
         
    Next cell
     
End Sub
0
 
LVL 24

Expert Comment

by:Steve
ID: 38775070
This should be faster and easier to use on a single cell...

paste the code into a new module in the workbook and then use =RemoveInCellDuplicates as a normal function.

Option Explicit

Function RemoveInCellDuplicates(inString As String) As String
Dim arr, x As Long
Dim Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")

arr = Split(inString, ",")

For x = 0 To UBound(arr)
    If Not Dic.exists(arr(x)) Then Dic.Add arr(x), arr(x)
Next x

RemoveInCellDuplicates = Join(Dic.Items, ",")

End Function

Open in new window

RemoveInCellDuplicates.xlsm
0
 

Author Comment

by:model_un
ID: 38775092
Thank you Emes.

I'm getting a run time error '5'.

When I go to debug, it is highlighting the following line:  finval = Left(finval, Len(finval) - 1)

Solution?

FF
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:model_un
ID: 38775168
Thank you The_Barman.

I was about to accept your solution when I found a hiccup... it would seem that when the first number is repeated the function does not remove the duplicate.

For example:

328, 328, 482, 482  is coming back 328, 328, 482

Any ideas?

FF
0
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 38775214
it must be leading or trailing spaces... try...

Option Explicit

Function RemoveInCellDuplicates(inString As String) As String
Dim arr, x As Long
Dim Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")

arr = Split(inString, ",")

For x = 0 To UBound(arr)
    If Not Dic.exists(trim(arr(x))) Then Dic.Add trim(arr(x)), trim(arr(x))
Next x

RemoveInCellDuplicates = Join(Dic.Items, ",")

End Function

Open in new window

RemoveInCellDuplicates.xlsm
0
 

Author Closing Comment

by:model_un
ID: 38775240
Excellent. Many Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question