Counting number of occurrences.

[a.riva@UCL]

Thanks for all the suggestions!

Now I have an other question...

I have my usual option-based-1 array1 in VBA, which contains x
numbers. Some of them are repeated. What I would like to do is
creating an other option-based-1 array, let's call it array2, which
contains the numbers of occurrences of each of the repeated elements
of array1 within array1... I'm struggling to find a solution...

For example:

option-based-1 array1 is (1, 2, 4, 6, 5, 4, 7, 2, 3, 2, 3) --> I
cannot sort the array.

I think that the procedure should do the following operation: it
detects how many items are repeated in "array1", and for each of this
repeated items stores in a new array "array2" a number corresponding
to the number of its occurrences.

For example, in array1 the procedure detects that there are n=3 items
which occur more than once (they are "2", "4" and "3"). Then it ReDims
array2 (1 to n), and for i=1 to n it gives to array2(i) the values of:

i=1 --> array2(1) = 3 (occurrences of "2"),
i=2 --> array2(2) = 2 (occurrences of "4"),
i=3 --> array2(3) = 2 (occurrences of "3").

Can somebody help me?

Thanks in advance

Antonio.

 

[Myrna Larson]

This is a "brute force" approach of which I'm not particularly proud, but it
works. I have assumed the values in Ary1 are integers, as you show. If they
aren't, this won't work as written. I print the duplicated values and their
counts to the immediate window. You'll probably want to do something else with
them...

Option Base 1
Option Explicit

Sub TestIt()
Dim Ary1() As Long
Dim Dummy() As String
Dim i As Long

Dummy() = Split("1,2,4,6,5,4,7,2,3,2,3", ",")
ReDim Ary1(1 To UBound(Dummy) + 1)
For i = 0 To UBound(Dummy)
Ary1(i + 1) = CLng(Dummy(i))
Next i
Erase Dummy
ShowDuplicates Ary1()
End Sub

Sub ShowDuplicates(Ary1() As Long)
Dim Ary2() As Long
Dim hi As Long
Dim i As Long
Dim j As Long
Dim lo As Long
Dim Temp() As Long

lo = Application.Min(Ary1)
hi = Application.Max(Ary1)
ReDim Temp(lo To hi)

'read values from 1st array and update counts in Temp array
For i = 1 To UBound(Ary1)
j = Ary1(i)
Temp(j) = Temp(j) + 1
Next i

j = 0
ReDim Ary2(1 To hi - lo + 1, 1 To 2) 'maximum possible entries

'make entries in Ary2 if count > 1
For i = lo To hi
If Temp(i) > 1 Then
j = j + 1
Ary2(j, 1) = i 'the value that's repeated
Ary2(j, 2) = Temp(i) 'how many occurrences
End If
Next i

If j = 0 Then MsgBox "No repeated values in 1st array"
Erase Temp()

'note that Ary2 has empty rows
For i = 1 To j
Debug.Print Ary2(i, 1), Ary2(i, 2)
Next i
End Sub