-1

SOLVED. Solution at end.

NOTE: I would like to receive the occurrences of each individual unique value, not the total number of unique values in a range.

I am trying to think of a good way to copy unique values from a range into either a 2-d array or a single-array, and then in either another column of the 2-d or a separate array, count the occurrences of each individual unique value in the original range.

I know I can use advanced filter to copy to a different range and read that in but I'm trying to, if possible, skip that step and store it directly into an array.

As for counting, I'm not entirely sure how to do that in VBA using this method at all.

EDIT: Thanks for telling me to use a dictionary, and to use a version of counting unique variables, but neither of those answer the question.

Example:

Range A 1, 1, 1, 2, 3, 4, 4, 5, 5, 5, 5, 6, 6, 7, 8, 9

I would like an output into an array, such that:

For 1-d arr1(x) from 0-max number of unique variables arr1(0)=1, arr1(1)=2, etc. and in a second array, using the same element numbers count the occurrences: arr2(0)=occurrences of arr1(0) in Range A, in this case 3.

For 2-d, which I imagine would be simpler, replace the single-reference with r,c and have a single array.

Solution:

Sub CreateList()
Dim dic As Object, vData As Variant, i As Long
Dim LastRow As Long

Dim Array1 As Variant

LastRow = Sheets("Raw").Cells(Rows.Count, "D").End(xlUp).Row

'Create a dictionary object
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
'Pass the data to a variant array to speed processing
vData = Sheets("Raw").Range("D2:D" & LastRow + 1)
'Loop
For i = 1 To UBound(vData)
    If vData(i, 1) <> "" Then dic(vData(i, 1)) = Empty
Next i
'Pass the unique keys to a 0 based array
Array1 = dic.keys

Sheets("AlarmCounts").Range("B2:B" & UBound(Array1) + 2).Value = Application.Transpose(Array1)

Call CountArray(Array1)

Call SortRange

End Sub

Sub CountArray(Array1)
Dim LastRow As Long

LastRow = Sheets("Raw").Cells(Rows.Count, "D").End(xlUp).Row

Dim i As Integer
Dim Array2() As Variant

ReDim Array2(UBound(Array1))

Dim iVal As Integer

For i = LBound(Array1) To UBound(Array1)
    With Sheets("Raw")
        iVal = Application.CountIf(.Range("D2:D" & LastRow), Array1(i))
        Array2(i) = iVal
    End With
Next i

Sheets("AlarmCounts").Range("C2:C" & UBound(Array1) + 2).Value = Application.Transpose(Array2)

Call AvgLen(Array2)

End Sub
JvdV
  • 70,606
  • 8
  • 39
  • 70
ExJester
  • 1
  • 1
  • 1
    Use a dictionary. – Scott Craner Jan 03 '20 at 20:05
  • 3
    Does this answer your question? [Count unique values in Excel](https://stackoverflow.com/questions/1676068/count-unique-values-in-excel) See the VBA answer for how to use a dictionary. You may have to tweak a bit. – BigBen Jan 03 '20 at 20:05
  • @ExJester - closing was partly my fault, sorry for my haste, that is not exactly a duplicate and as such I've voted to reopen the question. However you definitely can use a dictionary and just update the count of each item as you loop through items. – BigBen Jan 03 '20 at 20:54
  • 1
    @BigBen No worries, that gave me a good checksum to ensure data integrity, so it wasn't useless at all. I was able to find my answer using a dictionary, it just took some time. I will update my question with the resolution. – ExJester Jan 03 '20 at 22:49

1 Answers1

0

Try this approach:

Dim uniques As New Collection
Dim searchRange As Range, sr As Range

'Set searchRange = Range() ' assign the range

On Error Resume Next

For Each sr In searchRange
    uniques.Add sr, sr
Next
Err.Clear

On Error GoTo 0

And you'll get a collection of unique values. But there are some limitations - see the Collections documentation.

Vitaliy Prushak
  • 1,057
  • 8
  • 13