Removing Duplicates in an Array

Your code is reading data only from L1 (Line 2)

and writing to C1  (Line 16)

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

Select allOpen in new window

Regards

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

Select allOpen in new window

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.

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

Expert Martin Liss Program meets the requirements. It removes the duplicates in the whole used range.
This is an acceptable solution. Thanks.

He understood the requirements clearly even with insufficient clarity.

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

Unfortunately Expert Rgonzo1971 program neither removes duplicate nor makes any change in any cell. Hence it is not acceptable solution.

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.

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.

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.