Converting countif to VBA dictionary

[Guinaba]

Hi experts,

I have the formula below helping me to create an index col in my sheet, However, this is making my code too slow.

Does anyone know how to convert it to dictionary structure?

With Range("c2", Range("c" & Rows.Count).End(xlUp))

.Offset(0, -2).FormulaR1C1 = "=COUNTIF(R1C[2]:RC[2], RC[2])"

End With

 

[DanteAmor]

How about:

Sub test()
  Dim a As Variant, b As Variant
  Dim dic As Object, i As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value2
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    dic(a(i, 1)) = dic(a(i, 1)) + 1
    b(i, 1) = dic(a(i, 1))
  Next
  Range("A2").Resize(UBound(a)).Value = b
End Sub

 

[Guinaba]

@DanteAmor that is awesome!! What a difference in terms of performance!! Nice piece of code!! Thanks a lot!!

 

[Guinaba]

Hi @DanteAmor,
I am trying to understand your code do you mind telling why do you need two arrays? Is one to be used for the dic. index the the other for the dic.value?

 

[DanteAmor]

I am trying to understand your code do you mind telling why do you need two arrays? Is one to be used for the dic. index the the other for the dic.value?

Technically there are 3 matrices, a) to store the original data, b) to store the results, dic) to store the keys and the counter of each key.

 

[Guinaba]

Thanks Dante! Really liked it, I am going to learn more dictionary.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.