The creation of dynamic lists or strings of CSVs.

[Mhowells]

Hello all,

I need to create a dynamic list to feed into a pull-down menu that I am working on. As far as I can see there are two main ways that this data can be entered, so suggestions for either method would be extremely welcome.

The initial data represents a column of information in a dynamic body of data ranging from 100 to 1000 rows. In this column, there are between 20 and 30 fruit types.... Now, if I use this column as the basis of the pull-down list, it will show "Oranges" 10-20 times in the list, whereas I would like each item to be shown only once, preferably in alphabetical order.

To this end I need to either create a dynamic list (multiple rows) in which each entry is represented once, OR I need to create a dynamic string, made up of each of the entries in a comma-separated list. Either would work fine, but I can't imagine how to do it. Any suggestions?

Best,
Max

 

[wongm003]

Here is code I used to value a form list box to exclude blanks and duplicate values... There might be a faster way. This way goes through each row and checks to see if an item is in a dictionary object (dictionary object uses a key as index - see help for more info) and if the item is not in the dictionary object adds the item to the array.

Private Sub UserForm_Initialize()
On Error Resume Next

    Set d = CreateObject("Scripting.Dictionary")
    cn = 2 'Enter column that will be used for list values
    lr = 1000 'Enter last row in

    For rn = 2 To lr
        If Not d.exists(Cells(rn, cn).Value) And Cells(rn, cn).Value <> "" Then
            d.Add Cells(rn, cn).Value, Cells(rn, cn).Value
        End If
    Next rn

    a = d.items
    call insertion_sort(a)

    Me.ListBox1.list = a
    
End Sub

Private Sub insertion_sort(arList)
On Error Resume Next

    Dim varTemp As Variant
    
    For x = LBound(arList) + 1 To UBound(arList)
        varTemp = arList(x)
        y = x
    
        blnFlag = True
        Do While blnFlag
            If y = LBound(arList) Then
                blnFlag = False
            ElseIf StrComp(varTemp, arList(y - 1), vbTextCompare) > -1 Then
                blnFlag = False
            Else
                arList(y) = arList(y - 1)
                y = y - 1
            End If
        Loop
        arList(y) = varTemp
    Next x
End Sub

 

[Brian from Maui]

see,

http://www.mrexcel.com/board2/viewtopic.php?t=8659