Is there a method to find distinct values in a column

[Guest]

Dear all,

I have encountered a problem which makes me frustrated. I need to find out
all the distinct values in a specific column of an Excel worksheet. How can I
do it programatically? Any suggestion? Please advise.
Thanks in advance!

Ivan

 

[Guest]

Ivan,
Look at the VBA "Find" function - see VBA Help.

Can you give n example of what are "distinct" values?

 

[Guest]

Hi,

For example, in column A, I have the values:

Column A
-----------

A01
B02
C05
A01
D28
B02

Then the distinct values are A01,B02,C05 and D28
It has the same purpose as in SQL statement "select distinct(myField) from
myTable"

Can I do the same thing in Excel?

Ivan

 

[Guest]

Ivan,
This is code from a previous posting. Unique items are output
to new worksheet in ascending order.

HTH

Sub GetList()
Dim dic As Scripting.Dictionary
Dim rngSrc As Range
Dim rngDst As Range
Dim rngCel As Range

Set dic = New Scripting.Dictionary
dic.CompareMode = TextCompare 'CaseInsensitive

Set rngSrc = Range("a1:a10") ' <==== Change for your needs
On Error Resume Next
For Each rngCel In rngSrc.Cells
With rngCel
dic.Add Trim(.Value), .Value
End With
Next
dic.Remove vbNullString
On Error GoTo 0

Set rngDst = SetRange("myoutput")
With rngDst
.Resize(rngSrc.Rows.Count).Clear
With .Resize(dic.Count, 1)
.Name = "myoutput"
.Value = Application.Transpose(dic.Items)
.Sort .Columns(1), xlAscending
End With
End With

End Sub

Private Function SetRange(sRngName As String) As Range
On Error Resume Next
Set SetRange = Range(sRngName)
If SetRange Is Nothing Then
Set SetRange = Worksheets.Add().Range("A1")
SetRange.Name = sRngName
End If
End Function

 

[davidm]

Ivan.

You need to isolate UNIQUE items. The easiest way to do this is by
deploying Advanced Filter: Goto Data---->Filter---->Advanced Filter.
The facility is intuitively friendly.

However, if you want VBA solution, several are thinkable using loop or
otherwise. For example:


Sub GetUniquesInColA()
Dim rng as Range
Dim c

For each c in [a:a]
If not Isempty(c) then
If Application.Countif([a:a],c)>1 Then
If rng is Nothing then
set rng =c
Else
set rng=Union(c,rng)
End if
end if
Next

If Not rng is Nothing Then
rng.EntireRow.Delete
End if


Davidm

 

[DOR]

If you want a non-VBA solution that responds immediately to changing
values in your data range, you can create a list of the unique values
by entering the following formula in a cell in another column (assuming
your data range is named Data)

=INDEX($A:$A,SMALL(IF(MATCH(Data,$A:$A,0)=ROW(Data),ROW(Data),""),ROW(1:1)))

Enter it as an array formula with Ctl-Shift-Enter and drag down as far
as you need to get all unique values. If you drag beyond that point
you will get #NUM errors, which may or may not bother you.

If column A contains other values outside the range Data that interfere
with the above formula, then use this formula

=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data))),ROW(INDIRECT("1:"&ROWS(Data))),""),ROW(1:1)))

also entered as an array, and dragged down as far as necessary. This
formula may be longer but it is more reliable, given the potential for
extraneous data in the column.

You can eliminate the #NUM and replace them with blanks with this array
formula

=IF(ROW(1:1)>COUNT(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data))),ROW(INDIRECT("1:"&ROWS(Data))),"")),"",INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data))),ROW(INDIRECT("1:"&ROWS(Data))),""),ROW(1:1))))

or you could simplify the whole thing by using a helper column, say
column B. Put this (non-array) formula in B2, assuming your data
starts in A2,

=IF(COUNTIF($A$2:A2,A2)=1,ROW(),"")

and drag down to the end of your data. Then, in the first cell where
you want the unique list, enter

=IF(ROW(1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW(1:1))))

an drag down as far as necessary.

HTH

Declan O'R