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.

 

[Ron Coderre]

Where would you like the list of unique values to be built?
• Same worksheet?
• Eliminate duplicates from the original list?

Which approach would you like to use:
• a VBA program?
• Formulas?
• Advanced Filter?

 

[Richard Schollar]

Hi

Perhaps:

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
 
'you now have a collection of the unique items from your array:
 
For i = 0 to coll.Count - 1
  Cells(i,1) = coll(i)
Next i

 

[mikerickson]

Something like this should work.

Dim uniqueArray() as Variant
Dim pointer As Long, i As Long

pointer = LBound(AccountManagers)
Redim uniqueArray(pointer - 1 To UBound(AccountManagers))

For i = LBound(AccountManagers) To UBound(AccountManagers)
    If Not IsNumeric(Application.Match(AccountManagers(i), uniqueArray, 0)) Then
        pointer = pointer + 1
        uniqueArray(pointer) = AccountManagers(i)
    End If
Next i

ReDim Preserve uniqueArray(LBound(AccountManagers) To pointer)

 

[wisewood]

Thanks guys,

Mike, I get an out of range error with your code;

Dim AccountManagers As Variant
    xVal = Sheets("Details").Cells(Rows.Count, 1).End(xlUp).Row
    AccountManagers = Worksheets("Details").Range("D2:D" & xVal)
    Sheets("Summary").Range("S1").Value = UBound(AccountManagers)
 
Dim uniqueArray() As Variant
Dim pointer As Long, i As Long

pointer = LBound(AccountManagers)
ReDim uniqueArray(pointer - 1 To UBound(AccountManagers))

For i = LBound(AccountManagers) To UBound(AccountManagers)
    If Not IsNumeric(Application.Match(AccountManagers(i), uniqueArray, 0)) Then ' ERROR OUT OF RANGE HERE
        pointer = pointer + 1
        uniqueArray(pointer) = AccountManagers(i)
    End If
Next i

ReDim Preserve uniqueArray(LBound(AccountManagers) To pointer)

Richard, when i try and use what you gave me i get nothing at all.

 

[wisewood]

Ron,

Unique values should be listed into the "Summary" worksheet.
I'd prefer VBA, as there is a lengthy VBA program running to get up to this point... it selects a file based on user input, copies data from it into a worksheet, establishes which rows need to be included in the summary report, adds those rows to an array, empties the array into a "Details" worksheet, formats & sorts the entries in the Details worksheet, and then the end product is to prepare a summary page to email to the account managers.

 

[Richard Schollar]

Make sure you turn off the On Error Resume Next otherwise you will not notice if errors are occurring. In my code above, one glaring error was that Collections are not zero based!
Should have been:

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
 
'you now have a collection of the unique items from your array:
 
For i = 1 to coll.Count
  Cells(i,1) = coll(i)
Next i

 

[wisewood]

Thanks Richard.

I'm not getting any values added to the coll. I get no results output to the worksheet at the end so i added a line to output coll.Count after the collection of unique items has been established. It returns 0. But the count of entries in AccountManagers array is 128.

 

[Richard Schollar]

What example values do you have in AccountManagers? If it is an array of numeric values then it will fail (I made a dangerous assumption that it would be string values). The key for each collection item must be a string. To get around this you just need to amend the Add code to:

Dim coll As New Collection
 
 
On Error Resume Next
For i = Lbound(AccountManagers) to UBound(AccountManagers)
coll.Add AccountManagers(i),CStr(AccountManagers(i))
Next i
On Error Goto 0
 
'you now have a collection of the unique items from your array:
 
For i = 1 to coll.Count
Cells(i,1) = coll(i)
Next i

 

[wisewood]

Hi Richard,

Yes, you were right, they are string values. Account Manager names - Rita, Sue, Bob, etc.