Hello i need help about detecting any series items in a list box.
suppose two or more series items are

"glass01"
"glass02"
"glass04"
"glass03"
"glass05"
"marvel"
"apple"
"mango001"
"mango002"
"mango003"
"mango004"
"book"
"pen"

these items are list1 item. the series may be in different string. here two series glass and mango or other different series. now if i click cmd then detect series items and move to another listbox. I hope anyone will help about this

First we'd need to know, how do you define a series. Is a series simply where the first X amount of characters match another entry? Or will all series items have an appended numerical value. If the latter is the case then a simple numerical check and compare would sort it for you.

Please supply some more details and I'd be happy to help.

i mean if the items character and lenth are same then i may be a seris

I would use the following... it might have errors as It's something I quickly knocked together.

But in my test it loaded your data in to ListBox1 and then it sorts through and finds duplicates based on non-numeric matches only. If it finds a match it adds it to ListBox2, for efficiency (in larger lists) I got it to check to see if it a match has already been discovered too.

Hope this helps :)

Sub SortDuplicates()

temp = Array("glass01", "glass02", "glass04", "glass03", "glass05", "marvel", "apple", "mango001", "mango002", "mango003", "mango004", "book", "pen")

For Each i In temp
    ListBox1.AddItem i
Next i

For x = 1 To ListBox1.ListCount
    temp = ListBox1.List(x - 1)
    For y = 1 To Len(temp)
        If IsNumeric(Mid(temp, y, 1)) Then
            temp = Mid(temp, 1, y - 1)
                For c = 1 To ListBox1.ListCount
                    If Mid(ListBox1.List(x - 1), 1, Len(temp)) = temp Then
                        For z = 1 To ListBox2.ListCount
                            AlreadyDone = False
                            If Mid(ListBox2.List(z - 1), 1, Len(temp)) = temp Then
                                AlreadyDone = True
                                Exit For
                            End If
                        Next z
                        If AlreadyDone = False Then ListBox2.AddItem temp
                        Exit For
                    End If
                Next c
            Exit For
        End If
    Next y
Next x

End Sub
This article has been dead for over six months. Start a new discussion instead.