Solved

Remove duplicate within cell.

Posted on 2013-01-14
6
375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

634 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