Search for duplicates in combobox, get the listindex and remove it.

[NichoD]

Hello, I have a userform with different comboboxes. Combobox1 is filled with items where the first 6 charachters should differ from each other and the last thre charachters are either "DUB" or "TRU". If the first 6 charachters however are a duplicate, I want to remove the one ending with "DUB", as well as removing the item with same index in both combobox 2 and 3.


Thanks in advance!

 

[John_w]

Put this routine in your userform module and call it where/when needed. It uses 2 dictionaries - one for the items in ComboBox1 and one for their first 6 characters, in order to identify the duplicates.

Private Sub Remove_Duplicates()

    Dim cb1Dict As Object 'Scripting.Dictionary
    Dim cb1L6Dict As Object 'Scripting.Dictionary
    Dim i As Long, n As Long
    
    Set cb1Dict = CreateObject("Scripting.Dictionary") 'New Scripting.Dictionary
    Set cb1L6Dict = CreateObject("Scripting.Dictionary") 'New Scripting.Dictionary
    
    With Me.ComboBox1
        
        'Add all ComboBox1 items to cb1Dict dictionary
        
        For i = 0 To .ListCount - 1
            cb1Dict.Add .List(i), i
        Next
        
        'Loop through ComboBox1 items in reverse order, adding first 6 characters of each to cb1L6Dict dictionary
        
        For i = .ListCount - 1 To 0 Step -1
            
            'Are first 6 characters of this item unique?
            
            If cb1L6Dict.Exists(Left(.List(i), 6)) Then
            
                'No, so find item (key in cb1Dict) which starts with first 6 characters of this item and ends with "DUB" and remove it from ComboBox1
                'and the item in ComboBox2 and ComboBox3 with the same index
                          
                For n = cb1Dict.Count - 1 To 0 Step -1
                    'Debug.Print n, cb1Dict.Keys()(n), "First 6 = " & Left(cb1Dict.Keys()(n), 6), "Last 3 = " & Right(cb1Dict.Keys()(n), 3)
                    If Left(cb1Dict.Keys()(n), 6) = Left(.List(i), 6) And Right(cb1Dict.Keys()(n), 3) = "DUB" Then
                        'Debug.Print "Remove index " & cb1Dict.Items()(n) & " with key " & cb1Dict.Keys()(n)
                        .RemoveItem cb1Dict.Items()(n)
                        Me.ComboBox2.RemoveItem cb1Dict.Items()(n)
                        Me.ComboBox3.RemoveItem cb1Dict.Items()(n)
                        cb1Dict.Remove cb1Dict.Keys()(n)
                    End If
                Next
                    
            Else
            
                'Yes, so add first 6 characters of this item to cb1L6Dict dictionary
                
                cb1L6Dict.Add Left(.List(i), 6), i
                
            End If
            
        Next
        
    End With
    
End Sub

 

[NichoD]

im cb1Dict As Object 'Scripting.Dictionary Dim cb1L6Dict As

Put this routine in your userform module and call it where/when needed. It uses 2 dictionaries - one for the items in ComboBox1 and one for their first 6 characters, in order to identify the duplicates.

Private Sub Remove_Duplicates()

    Dim cb1Dict As Object 'Scripting.Dictionary
    Dim cb1L6Dict As Object 'Scripting.Dictionary
    Dim i As Long, n As Long
    
    Set cb1Dict = CreateObject("Scripting.Dictionary") 'New Scripting.Dictionary
    Set cb1L6Dict = CreateObject("Scripting.Dictionary") 'New Scripting.Dictionary
    
    With Me.ComboBox1
        
        'Add all ComboBox1 items to cb1Dict dictionary
        
        For i = 0 To .ListCount - 1
            cb1Dict.Add .List(i), i
        Next
        
        'Loop through ComboBox1 items in reverse order, adding first 6 characters of each to cb1L6Dict dictionary
        
        For i = .ListCount - 1 To 0 Step -1
            
            'Are first 6 characters of this item unique?
            
            If cb1L6Dict.Exists(Left(.List(i), 6)) Then
            
                'No, so find item (key in cb1Dict) which starts with first 6 characters of this item and ends with "DUB" and remove it from ComboBox1
                'and the item in ComboBox2 and ComboBox3 with the same index
                          
                For n = cb1Dict.Count - 1 To 0 Step -1
                    'Debug.Print n, cb1Dict.Keys()(n), "First 6 = " & Left(cb1Dict.Keys()(n), 6), "Last 3 = " & Right(cb1Dict.Keys()(n), 3)
                    If Left(cb1Dict.Keys()(n), 6) = Left(.List(i), 6) And Right(cb1Dict.Keys()(n), 3) = "DUB" Then
                        'Debug.Print "Remove index " & cb1Dict.Items()(n) & " with key " & cb1Dict.Keys()(n)
                        .RemoveItem cb1Dict.Items()(n)
                        Me.ComboBox2.RemoveItem cb1Dict.Items()(n)
                        Me.ComboBox3.RemoveItem cb1Dict.Items()(n)
                        cb1Dict.Remove cb1Dict.Keys()(n)
                    End If
                Next
                    
            Else
            
                'Yes, so add first 6 characters of this item to cb1L6Dict dictionary
                
                cb1L6Dict.Add Left(.List(i), 6), i
                
            End If
            
        Next
        
    End With
    
End Sub

Thank you so much! This was the exact sollution I was looking for! Have a great day!