Unique array values

wisewood

Board Regular
Joined
Nov 7, 2002
Messages
193
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.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Perhaps:

Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,780
Something like this should work.
Code:
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

Board Regular
Joined
Nov 7, 2002
Messages
193

ADVERTISEMENT

Thanks guys,

Mike, I get an out of range error with your code;
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

Board Regular
Joined
Nov 7, 2002
Messages
193
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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:
Code:
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

Board Regular
Joined
Nov 7, 2002
Messages
193
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Rich (BB code):
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

Board Regular
Joined
Nov 7, 2002
Messages
193
Hi Richard,

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

Watch MrExcel Video

Forum statistics

Threads
1,109,049
Messages
5,526,488
Members
409,703
Latest member
nbkqsj7

This Week's Hot Topics

Top