Trying to list unique values in an arryay

D

Dan S

Here is what I am trying to do. I have an array consisting of 6 columns and 40 rows. How can I look in that array and list the unique values of it. I don't want the count, but the unique values.

Thanks.
DRS
 
A

AlfD

Hi!

Easiest way is probably to put all the values into one column (o
another worksheet, for example) and use Data > Filter > Advance
Filter... to produce the unique values.

Al
 
D

Dave Peterson

One way is to use a Collection:

Option Explicit
Sub testme()

Dim uniqColl As New Collection
Dim myCell As Range
Dim iCtr As Long

With ActiveSheet
On Error Resume Next
For Each myCell In .Range("a1:f40")
If Trim(myCell.Value) <> "" Then
uniqColl.Add Item:=CStr(myCell.Value)
End If
Next myCell
On Error GoTo 0

For iCtr = 1 To uniqColl.Count
MsgBox uniqColl(iCtr)
'or whatever you want to do with it.
Next iCtr
End With
End Sub
 
P

pikapika13

I agree w/ Alf...but here's a cruder possibility: If you're trying t
find unique values for that entire array (vs. columns only) you ca
also try entering this formula in Cell G1:
=IF(COUNTIF($A$1:$F$40,A1)=1,A1,"")
Auto copy that over 6 columns and across and 40 down
 
A

Alan Beban

Dan said:
Here is what I am trying to do. I have an array consisting of 6 columns and 40 rows. How can I look in that array and list the unique values of it. I don't want the count, but the unique values.

Thanks.
DRS
Click to expand...
I take it by "array" you mean a range of values. If the functions in
the freely downloadable file at http://home.pacbell.net/beban are
available to your workbook, the following, arrray entered into a column
of cells sufficently large to accommodate the unique values, will list them:

=ArrayUniques(A1:F40, True)

For a listing in a single row:

=ArrayUniques(A1:F40)

Alan Beban
 
Top