• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

compare value in an array

hi,
   I've stuck on a process which need to compare values in a two dimensional array, to determine how many different unique identities are there in such array. would anybody help me with that?
I am reading data into array through database, and it returns something like this.
counting(1,0) = "Mike"
counting(2,0) = "jack"
.
.
.
counting(8,0) = "Mike" --> theres a duplicate
.
.
.
the size of array varied each time i run the solution. but i managed to have all the names in
counting(i,0). if that helps. any help would be appreciated;)

and eventually i need to create a list of unique Names, so that'll minors the range down to like 10 persons in total.
0
miketonny
Asked:
miketonny
  • 3
1 Solution
 
hosneylkCommented:
iterate through each element in the array and copy the element to a new array. before adding it to the new array check if the new array already contains it. if it does don't copy it.
0
 
hosneylkCommented:

Dim unique() As String
        ReDim unique(0)
        Dim valExists As Boolean
        For Each Val As String In counting
            valExists = False
            For Each uniqueVal As String In unique
                If (uniqueVal = Val) Then
                    valExists = True
                    Exit For
                End If
            Next
            If Not valExists Then
                ReDim Preserve unique(unique.Length + 1)
                unique(UBound(unique)) = Val
            End If
        Next

Open in new window

0
 
Daz_1234Commented:
Hi,

You can also use the dictionary object - dictionary keys must be unique.

The short script below is a example of using this method.

Hope this helps,
Daz.
Dim Counting(50,1)

Set dicList = CreateObject("Scripting.Dictionary")

Counting(0,0) = "Mike"
Counting(1,0) = "Jon"
Counting(2,0) = "Mike"
Counting(3,0) = "Daz"
Counting(4,0) = "Mary"
Counting(5,0) = "Mike"


For i = 0 To UBound(Counting, 1)
    If Counting(i, 0) <> "" Then
        dicList(Counting(i, 0)) = 1
    End If
Next

MsgBox Join(dicList.Keys, " / "),, "Unique Names = " & dicList.Count

Open in new window

0
 
miketonnyAuthor Commented:
hi hosneylk,
   it did give me the counts in array, but it included all the irrelevant data, as it's a two dimensional array, i do know the usernames are only stored in (1,0), (2,0), ... (x,0). but what you did stores pretty much every different value into the new array, how do i sort them out?
0
 
hosneylkCommented:

Dim unique() As String
        Dim strVal As String
        ReDim unique(0)
        Dim valExists As Boolean
        Dim i As Long
        For i = LBound(counting, 1) To UBound(counting, 1)
            valExists = False
            strVal = counting(i, 0)
            For Each uniqueVal As String In unique
                If (uniqueVal = strVal) Then
                    valExists = True
                    Exit For
                End If
            Next
            If Not valExists Then
                ReDim Preserve unique(unique.Length + 1)
                unique(UBound(unique)) = strVal
            End If
        Next

Open in new window

0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now