Removing Duplicates in an Array

I have strings that looks like "apples//apples//oranges//", "Oranges//" in various cell of the worksheet . It ts a  sample data to test vba program functioning.   I'm not getting any error nor data is written back to sheet correctly after removing duplicates. Where I am going wrong?

 
Sub DeDup()
    duplicateArray = Split(Sheets("Sheet1").Cells(1, 12).Value, "//")
    Dim c As Collection
    Set c = New Collection
    On Error Resume Next

    For Each d In duplicateArray
        c.Add d, CStr(d)
    Next d

    programsArray = c(1)
    For i = 2 To c.Count
        programsArray = programsArray & "//" & c(i)
    Next i

    Sheets("Sheet1").Cells(1, 3).Value = programsArray
End Sub

Open in new window

removedup.xlsm
Sunil KakkarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Your code is reading data only from L1 (Line 2)

and writing to C1  (Line 16)
Rgonzo1971Commented:
Hi,

pls try

Sub DeDup()
    duplicateArray = Split(Sheets("Sheet1").Cells(1, 12).Value, "//")
    Dim c As Object
    Set c = New Collection
    Dim aProgramsArray As Variant
    On Error Resume Next

    For Each d In duplicateArray
            c.Add d, d
    Next d

    ReDim aProgramsArray(c.Count - 1)
    For Idx = 0 To c.Count - 1
        aProgramsArray(Idx) = c(Idx + 1)
    Next
    Sheets("Sheet1").Cells(1, 3).Value = Join(aProgramsArray, "//")
End Sub

Open in new window

Regards
Martin LissOlder than dirtCommented:
Option Explicit
Sub DeDup()

    Dim duplicateArray() As String
    Dim programsArray As String
    Dim c As Collection
    Set c = New Collection
    Dim d As Long
    Dim cel As Range
    
    For Each cel In Range("A1:O" & Sheets("Sheet1").UsedRange.Rows.Count)
        If cel <> "" Then
            duplicateArray = Split(cel.Value, "//")
            
            On Error Resume Next
        
            'For Each d In duplicateArray
            For d = 0 To UBound(duplicateArray)
                c.Add duplicateArray(d), CStr(duplicateArray(d))
            Next d
        
            programsArray = c(1)
            For d = 2 To c.Count
                programsArray = programsArray & "//" & c(d)
            Next d
        
            cel.Value = programsArray
            Set c = New Collection
        End If
    Next
End Sub

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Sunil KakkarAuthor Commented:
I am new to expert-exchange and this is my first question. I do not fully understand at present customs and practices here so I will follow the way I am doing at other forums. Expert Saqib Husain, Syed has rightly pointed out that my data is reading from L1 and writing to C1. Further testing reveals that even for these positions it is not removing duplicates.  He has analysed correctly but suggestion to proceed further would be welcome. I am keeping this issue open till we review other experts opinions and their suggestions.
Martin LissOlder than dirtCommented:
Did you try my code in post ID: 40980020? If so and it doesn't do what you want, please tell me what the problems are.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
If you enter

almonds//walnuts//kishmish//kaju//grapes//banana//papaya//pomegranate//peas//almonds//oranges//apples//kaju//apples//apples//Oranges

in L1 and run your original code then you will get the correct result in  C1
Sunil KakkarAuthor Commented:
Expert Martin Liss Program meets the requirements. It removes the duplicates in the whole used range.
This is an acceptable solution. Thanks.
Sunil KakkarAuthor Commented:
He understood the requirements clearly even with insufficient clarity.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
Sunil KakkarAuthor Commented:
Unfortunately Expert Rgonzo1971 program neither removes duplicate nor makes any change in any cell. Hence it is not acceptable solution.
Sunil KakkarAuthor Commented:
Expert Saquib Please appreciate data is received in the spreadsheets from various sources and additional efforts for customizing are not desirable. I thank you for having shown interest in solving the problem.
Saqib Husain, SyedEngineerCommented:
No worries, I thought you were trying to learn to write code as your question was
Where I an going wrong?
So I told you where.


You also wrote
... nor data is written back to sheet correctly after removing duplicates
I responded to that too

I never attempted to "solve" the problem for you because you did not ask for it.
Sunil KakkarAuthor Commented:
Dear Saquib I appreciate your feedback ,  I will try to frame my questions with more clarity and defined objectives. I hope to be regularly interacting on this forum. I look forward to fruitful association with you. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.