listbox to remove dups and count matches in column

[buckwheat4948]

I have a problem that is a little complex for me.

I have a spreadsheet with data, say cells $A$11:$A$30 that each cell contains, say one of 5 different userids. So one userid could be there 5 times, another one twice, another one once etc.

Id like to have cell A5 have a drop down list that contains one of each of the userids found in cells $A$11:$A$30 ie: remove the duplicates.

Id then like to have A6 display the total of userid's found from cells $A$11:$A$30 when the user clicks on the userid from dropdown list in cell A5.


And to top it all off, the sheet that this is on is created through VBA automatically. So the formulas needs to added to the sheet through VBA at sheet creation time. Is it possible to do this by using .formula?

Can someone help me? Many thanks,

Mike

 

[AlphaFrog]

Hi and welcome.

Try something like this...

    Dim strList As String
    
    Application.ScreenUpdating = False

    For Each cell In Range("A11:A30")
        If cell.Value <> "" Then
        If InStr(1, strList, cell.Value & ",", 1) = 0 Then strList = strList & cell.Value & ","
        End If
    Next cell
    strList = Left(strList, Len(strList) - 1)
    
    With Range("A5").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=strList
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
    
    Range("A6").Formula = "=IF(A5="""","""",COUNTIF(A11:A30,A5))"
    
    Application.ScreenUpdating = True

 

[buckwheat4948]

Excellent. I got it working perfectly. Thanks. Can we add something to it though, is there a way to sort the validation lists so they are in alphabetical order?

thanks again

 

[AlphaFrog]

    Dim strList As String, vList As Variant
    Dim i As Long, j As Long
    
    'Uniques
    For Each cell In Range("A11:A30")
        If cell.Value <> "" Then
        If InStr(1, strList, cell.Value & ",", 1) = 0 Then strList = strList & cell.Value & ","
        End If
    Next cell
    strList = Left(strList, Len(strList) - 1)
    
    'Sort
    vList = Split(strList, ",")
    For i = 0 To UBound(vList)
       For j = i To UBound(vList)
            If LCase(vList(j)) < LCase(vList(i)) Then
                strList = vList(i)
                vList(i) = vList(j)
                vList(j) = strList
            End If
        Next j
    Next i
    strList = vList(0)
    For i = 1 To UBound(vList)
        strList = strList & "," & vList(i)
    Next i
    
    Application.ScreenUpdating = False
    
    With Range("A5").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=strList
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
    
    Range("A6").Formula = "=IF(A5="""","""",COUNTIF(A11:A30,A5))"
    
    Application.ScreenUpdating = True

 

[buckwheat4948]

Awesome, works perfect.

Thanks for the super fast reponses, you make it look so easy.

A million thanks.

 

[AlphaFrog]

You're welcome.

 

[buckwheat4948]

hey alpha frog,

Is it possible to expand this again?

Is it possible to use the formula2 for the validation list to sort rows 10:$20 based on the value of the validation list.

So the value selected from the validation list sorts rows 10:$30 sorting the validation list matches on top, then the remainer of the list below it.

so if A10-A30 contains 5 of the same values, dates in the format(06/16/2011), and the user clicks the validation list, the formula2 sorts rows 10-30, putting the 5 matches on top and the other 25 below it?

Ugh, hope that makes sence...

Thanks

 

[AlphaFrog]

You could sort rows 10:30 like you describe, but not by using the .Formula2 property of the validation list. You would have to create a separate sorting procedure that automatically runs when the user makes a change in the A5 DV list.

To automatically trigger that code when the user makes a change to cell A5, you could use the Worksheet_Change event procedure in the sheet module that has the Data Validation list.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$5" Then
        [COLOR="Green"]' Sort code goes here[/COLOR]
    End If
End Sub

And because you are supposedly creating this worksheet with code, you need to create the above procedure to the worksheet module in the new worksheet using VBA code to create the new procedure.
Extensibility: write code that modifies or reads other VBA code

I hope this helped. Good luck.

 

[buckwheat4948]

ah I was wondering how to do that. Thats what i tried doing before asking for help.

Thanks, ill give this a try and repost if i have any problems.

Thanks for pointing me in the right direction!