Help With Duplicates In A Combo Box 4

[Jdoggers]

hi,
i have a combo box that has about 500 items in it. These items have some duplicate entries in them. I was wondering if any of you know maybe an algorithm that would loop through the combo box, find the duplicates, and for each duplicate found that name would be added to another listbox. Oh, the listbox should only contain the names of the duplicate entries in the previous combobox, but the listbox shouldnt have any duplicate entries in it. So, in other words, the listbox will be all the names of the duplicate entries that were in the first combobox. Any suggestions?

thanks in advance

 

[HydraNL]

That's easy to do with SQL:

SELECT DISTINCT Field1 FROM Field2, activeconnection, etc, etc,

 

[Jdoggers]

thanks for responding, however im only limited to using visualbasic for this one...and im not working with any databases or spreadsheets. The data is coming from a folder on the c drive. Do you know of any way that i can loop through my combobox so i can get the desired result?

 

[Hypetia]

Collections are useful in finding duplicates.
___
[tt]
Dim N As Long, S As String
Dim C1 As New Collection, C2 As New Collection
On Error Resume Next
For N = 0 To Combo1.ListCount
S = Combo1.List(N)
C1.Add vbNullString, S
If Err = 457 Then 'duplicate key
C2.Add S, S
Err.Clear
End If
Next
Set C1 = Nothing
For N = 1 To C2.Count
List1.AddItem C2(N)
Next
Set C2 = Nothing[/tt]

 

[strongm]

>The data is coming from a folder on the c drive.

Erm ... in the VBA forum you are maintaining that the data comes from Excel

Could I suggest that you tell us where the data is really coming from, what appliation you are using a combobox in, and what you are really hoping to achieve?

It'll make it a lot easier for us to help you (and for you to decide which forum is most appropriate to deal with the problem)

 

[Jdoggers]

im sorry for all the confusion. The rowsource and rowsourcetype are both blank because im filling the combobox with code. here is some code to describe the process...combobox1 is the combobox that gets filled with all the files in the folder, without the path, or the extension, and listbox1 is the box that should be filled with nothing but a list of duplicates, but not have any redundant data. Example) lets say that i have 3 files called "dave101" in the folder, but with different extensions. this code below will fill the combobox with all the names of the files in the folder. What i need to do is take that list, and use it to make a new list in the listbox where, for example, in this case there will be one entry in the listbox that says "dave101" :



public sub fillboxes()
count2 = 0
strcount = 0


With Application.FileSearch
.NewSearch
.LookIn = "C:\myfolder\"
.SearchSubFolders = True
.Filename = "*.D*"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
End With

With Application.FileSearch
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.count & _
" file(s) found."
For i = 1 To .FoundFiles.count

'this part just is a string parsing operation, but not needed
part1 = Left(.FoundFiles(i), InStrRev(.FoundFiles(i), ".") - 1)
strcount = InStrRev(part1, "\")
part2 = Mid(part1, strcount + 1)

'this is where the items get added to the combobox
ComboBox1.AddItem part2

Next i
Else
'an error case if no files are in folder
MsgBox "There were no files found."
End If

For u = 0 To .FoundFiles.count - 1

'setfocus to the "u" position in the combobox, save it to variable
ComboBox1.ListIndex = u
part3 = ComboBox1.Text

'nested loop that looks for duplicates
For g = 0 To .FoundFiles.count - 1
ComboBox1.ListIndex = g


If part3 = ComboBox1.Text Then
count2 = count2 + 1

'if there was more than one (a duplicate entry)
If count2 > 1 Then
'add it to the list of duplicates
ListBox1.AddItem part3
count = 0
g = .FoundFiles.count - 1

End If

End If

Next g

Next u

End With
End Sub

this code almost works, but the only thing is i cant figure out how to stop the second listbox from having any duplicate entries...any help guys?

 

[strongm]

Given the code you are showing here, Hypetia's solution is probably the easiest to insert.

Essentially, where you do

ComboBox1.AddItem part2

You actually need to try and add part2 to a collection

Hypetia's
C1.Add vbNullString, part2

(the vbNullString is there because, for your purposes, we don't actually need to add anything real to the collection, we just need to ensure that we have a key)

If the key is already there you will get an error, in which case you don't want to add the item to the Listbox. Hypetia actaully adds items to a second collection and then dumps the contents of the second collection into the target Listbox:

If Err = 457 Then 'duplicate key
C2.Add part2, part2
Err.Clear
End If

But we can shortcut that with:

If Err = 457 Then 'duplicate key
Listbox1.Additem part2
Err.Clear
End If


Indeed, we can shortcut it even further by doing the test for duplication and the addition to the listbox at the same time as we populate the Combobox, so you end up with something like:

[blue]Public Sub fillboxes()
Dim C1 As New Collection
count2 = 0
strcount = 0


    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\myfolder\"
        .SearchSubFolders = True
        .FileName = "*.D*"
        .MatchTextExactly = True
        .FileType = msoFileTypeAllFiles
    End With
    
    With Application.FileSearch
    If .Execute() > 0 Then
       MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count

          'this part just is a string parsing operation, but not needed
             part1 = Left(.FoundFiles(i), InStrRev(.FoundFiles(i), ".") - 1)
             strcount = InStrRev(part1, "\")
             part2 = Mid(part1, strcount + 1)
             
                'this is where the items get added to the combobox
                  ComboBox1.AddItem part2
                  On Error Resume Next
                  C1.Add vbNullString, part2
                  If Err = 457 Then 'duplicate key
                        Listbox1.AddItem part2
                        Err.Clear
                    End If
                On Error GoTo 0

             
        Next i
    Else
       'an error case if no files are in folder
        MsgBox "There were no files found."
    End If
    
        
   
    End With
End Sub
[/blue]

 

[Hypetia]

Strongm, I think we need to maintain the use of C2 because we also do not want duplicates in the 'duplicate list'.

[tt]C2.Add S, S[/tt] ensures this. It prevents duplicate entries in C2. Later on when you add C2 items in the listbox, you get a unique list of duplicates.

If you bypass C2 and add items directly to listbox, you will end up with duplicate entries in listbox, if the number of instances of a duplicate item in combobox is 3 or more. I checked it in MSWord and it behaved the same way.

 

[strongm]

Shouildn't do. Don't need it all I use this technique all the time, and it works fine. Note that I'm not loading listbox from C2 later. I'm populating it at the same time as I populate the combobox, so I don't need the interim collection. That's my point. Here's a simplified version:

[blue]Option Explicit

Private Sub Command1_Click()
    Dim c1 As New Collection
    
    Dim InsertDemo As Variant

    For Each InsertDemo In Array("1", "1", "5", "2", "2", "2", "4", "1", "2", "5")
        On Error Resume Next
        Combo1.AddItem InsertDemo
        c1.Add vbNullString, InsertDemo
        If Err = 0 Then
            List1.AddItem InsertDemo
            Err.Clear
        End If
        On Error GoTo 0
    Next

End Sub[/blue]

 

[Hypetia]

Aah.. But this code is still not doing the task that is requested. It simply fills the listbox with the items in the combobox avoiding duplicates.

After running the code. I get 1, 5, 2 and 4 in the listbox.

I reread the original post. May be I misunderstood the question.

Oh, the listbox should only contain the names of the duplicate entries in the previous combobox, but the listbox shouldnt have any duplicate entries in it. So, in other words, the listbox will be all the names of the duplicate entries that were in the first combobox.

According to this, the listbox should receive only 1, 5 and 2. Not 4 because it is not duplicated in the original array.

Am I still missing something?

 

[strongm]

Quite right. I'll just go hide in the corner ...

 

[Jdoggers]

what you wrote was very helpful but the code that strongm wrote is ok except for that the number 4 should not be in the listbox, since it doesnt have any other 4's in the original array. the listbox should contain a 1, 2, and a 5 from the previous code. Is there any way to sort this out?

thanks guys

 

[Trevil]

WHOA! You've got different people working on the same issue -- this same question is in another thread:

http://www.tek-tips.com/viewthread.cfm?qid=1081993

Does the other solution not work?




Code: Where the vision is often rudely introduced to reality!

 

[Jdoggers]

thanks guys for all your help, i think the problem got solved. And, by the way, i gave everyone votes for helping me...