Unique array values

[wisewood]

Hi all,

I have an array, AccountManagers(x) containing, for example, 130 entries, of which there might only be something like 5-10 unique entries.

Can anyone tell me how i can pick out only the unique entries from this array?

Thanks.

 

[Richard Schollar]

What's the exact code you are using? It should work...

 

[wisewood]

    Dim AccountManagers As Variant
    xVal = Sheets("Details").Cells(Rows.Count, 1).End(xlUp).Row
    ReDim AccountManagers(xVal)
    AccountManagers = Worksheets("Details").Range("D2:D" & xVal)
    Sheets("Summary").Range("S1").Value = UBound(AccountManagers)
    
    Dim coll As New Collection
 
    On Error Resume Next
    For i = LBound(AccountManagers) To UBound(AccountManagers)
        coll.Add AccountManagers(i), AccountManagers(i)
    Next i
    On Error GoTo 0
     
    Sheets("Summary").Cells(1, 21) = LBound(AccountManagers)
    Sheets("Summary").Cells(1, 22) = UBound(AccountManagers)
    Sheets("Summary").Cells(1, 23) = coll.Count
     
    'you now have a collection of the unique items from your array:
     
    For i = 1 To coll.Count
        Sheets("Summary").Cells(i, 20) = coll(i)
    Next i

Sorry, bit abrupt, i'm off into a meeting in a minute.

 

[wisewood]

I've come up with a bit of a cheat solution ...

I've added an extra column to the worksheet containing the account manager names and copied this formula down to all rows =COUNTIF($D$2:D2,D2).

This way, I can then loop through and only pick up the Account Manager names for the array where the count = 1.

Maybe not the most elegant solution, but it should do the job well enough.

 

[Richard Schollar]

It was failing due to you having a 2 dimensional array - try the following modified code (note, your workaround is definitely not a bad solution, however!):

 Dim AccountManagers As Variant
    xVal = Sheets("Details").Cells(Rows.Count, 1).End(xlUp).Row
   ''' ReDim AccountManagers(xVal)  ====== Unnecessary
    AccountManagers = Worksheets("Details").Range("D2:D" & xVal)
    Sheets("Summary").Range("S1").Value = UBound(AccountManagers)
    
    Dim coll As New Collection
 
    On Error Resume Next
    For i = LBound(AccountManagers,1) To UBound(AccountManagers,1)
        coll.Add AccountManagers(i,1), AccountManagers(i,1)
    Next i
    On Error GoTo 0
     
    Sheets("Summary").Cells(1, 21) = LBound(AccountManagers)
    Sheets("Summary").Cells(1, 22) = UBound(AccountManagers)
    Sheets("Summary").Cells(1, 23) = coll.Count
     
    'you now have a collection of the unique items from your array:
     
    For i = 1 To coll.Count
        Sheets("Summary").Cells(i, 20) = coll(i)
    Next i