Extracting unique value from a column of an array

[Sachin2k6]

Hi guys
I have a two dimaentional array said ' My Array' in my vba procedure which stores person names in first column and cities names to which the person in 1st column has visited , in 2nd column.
My need is to count the unique city names in the 2nd column. How can i count that.
Data stored in the array is like this...
Name. City Visited
Sachin. Dhampur
Nitin. Moradabad
Neha. Dhampur
Sunil. Moradabad
.........
........
..........

Task is to count the unique cities in 2nd column of the array.

 

[Sachin2k6]

Any One!!!!!!

 

[Sachin2k6]

Any one please

 

[Marcelo Branco]

I would use a Dictionary - fast and efficient. Something like this

Dim MyArray As Variant, i As Long
Dim dic As Object, lCountUnique As Long

Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare

MyArray = Range("A2:B10")
For i = LBound(MyArray, 1) To UBound(MyArray, 1)
    If MyArray(i, 2) <> "" Then dic(MyArray(i, 2)) = Empty
Next i
lCountUnique = dic.Count
MsgBox lCountUnique

M.

 

[Repush]

. . . using string-comparison, -concatenation and Split . .

    For i = 2 To UBound(MyArray)
        If InStr("|" & CitiesStr, "|" & MyArray(i, 2) & "|") = 0 Then CitiesStr = CitiesStr & MyArray(i, 2) & "|"
    Next
    Cities = Split(CitiesStr, "|")
    n = UBound(Cities) - 1

 

[Repush]

sorry, Cities = 0-based
n = UBound(Cities)