Count Key with Dictionary using Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s post, we will explain you how to count key with dictionary using Excel VBA, as we explained in previous post How to Remove Duplicates with Dictionary using Excel VBA. In this article, we will explain to you how you can count key with dictionary many times many keys come many times, which we want to see, after how long the key has appeared in the data, as you can see below Data is given, in this we will use dictionary, so we have to add Microsoft runtime library.
In the below image you can see the data which we have to Count Key with Dictionary using Excel VBA.
Let us now understand How to Count Key with Dictionary using Excel VBA
How to Count Key with Dictionary using Excel VBA
Well, you must know that How to Count Key with Dictionary using Excel VBA We will learn in this article. VBE’s, let’s understand. So, we have to go like last time, first go to the Developer Tab then click on the option of Visual Basic as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
Microsoft Runtime Library must be activated to when using dictionary. to activate go to Tools option and then References and on that we select Microsoft Script Runtime Library and click OK.
Once the module is inserted, and the library is activated, we’ll write a subroutine to count key with Dictionary.
Sub CountByDictionary() End Sub
Declaring more variables which are using in the entire project for Long, Variant and worksheet.
Sub CountByDictionary() Dim lr As Long Dim i As Long Dim Key As Variant Dim WS As Worksheet End Sub
Here we are Declaring Instant variables for Dictionary.
Sub CountByDictionary() Dim lr As Long, i As Long Dim Key As Variant Dim WS As Worksheet Dim Dic As New Dictionary End Sub
Now we will set worksheet as ActiveSheet.
Sub CountByDictionary() Dim lr As Long, i As Long Dim Key As Variant Dim WS As Worksheet Dim Dic As New Dictionary Set WS = ActiveSheet End Sub
Here we will Declare last row.
Sub CountByDictionary()
Dim lr As Long, i As Long
Dim Key As Variant
Dim WS As Worksheet
Dim Dic As New Dictionary
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
End Sub
Here we will make a loop, which is the range. After adding the loop, we will define the range of KEY.
Sub CountByDictionary()
Dim lr As Long, i As Long
Dim Key As Variant
Dim WS As Worksheet
Dim Dic As New Dictionary
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
Key = WS.Range("A" & i).Value
Next i
End Sub
Now we will create a condition, if key does not exist in dictionary, then add 1.
Sub CountByDictionary()
Dim lr As Long, i As Long
Dim Key As Variant
Dim WS As Worksheet
Dim Dic As New Dictionary
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
Key = WS.Range("A" & i).Value
If Not Dic.Exists(Key) Then
Dic.Add Key, 1
End If
Next i
End Sub
Create second condition, if key exists in dictionary, then add one more to it.
Sub CountByDictionary()
Dim lr As Long, i As Long
Dim Key As Variant
Dim WS As Worksheet
Dim Dic As New Dictionary
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
Key = WS.Range("A" & i).Value
If Not Dic.Exists(Key) Then
Dic.Add Key, 1
Else
Dic(Key) = Dic(Key) + 1
End If
Next i
End Sub
Now we will make Another Loop. Take Key from column A that we declared above and paste it below.
Sub CountByDictionary()
Dim lr As Long, i As Long
Dim Key As Variant
Dim WS As Worksheet
Dim Dic As New Dictionary
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
Key = WS.Range("A" & i).Value
If Not Dic.Exists(Key) Then
Dic.Add Key, 1
Else
Dic(Key) = Dic(Key) + 1
End If
Next i
For i = 2 To lr
Key = WS.Range("A" & i).Value
Next i
End Sub
Create another condition. If key is Exist on Dictionary. So, it will add count to column D of the worksheet.
Sub CountByDictionary()
Dim lr As Long, i As Long
Dim Key As Variant
Dim WS As Worksheet
Dim Dic As New Dictionary
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
Key = WS.Range("A" & i).Value
If Not Dic.Exists(Key) Then
Dic.Add Key, 1
Else
Dic(Key) = Dic(Key) + 1
End If
Next i
For i = 2 To lr
Key = WS.Range("A" & i).Value
If Dic.Exists(Key) Then
WS.Range("D" & i).Value = Dic(Key)
End If
Next i
End Sub
Run the code and see if it is counting the keys.
After running the code, we will go to the worksheet, here you can see that the number of times the key is in the sheet is the number of times, as we can see the count of the key ‘Fionn Ryan’ here, the key is four times in the data, so you can count the count column. I can see the count value of Fionn Ryan.
Now we will find the running count of key. Here we will write the worksheet range for the count column.
Sub CountByDictionary()
Dim lr As Long, i As Long
Dim Key As Variant
Dim WS As Worksheet
Dim Dic As New Dictionary
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
'Get Running Count
For i = 2 To lr
Key = WS.Range("A" & i).Value
If Not Dic.Exists(Key) Then
Dic.Add Key, 1
Else
Dic(Key) = Dic(Key) + 1
End If
Next i
WS.Range("D" & i).Value = Dic(Key)
End Sub
Now we will run code, this will count the key according to the time if the key is first time, then 1 if the key is second time, then 2 and if the same data is third time, then 3 will count. as you can see the ‘Fionn Ryan’ Key.
Let’s see once we filter out the key ‘Fionn Ryan’. here the key is 4 times, now we will filter out.
Go back to VBE and now we will use VBTab to give range of second key as well as first key.
Sub CountByDictionary()
Dim lr As Long, i As Long
Dim Key As Variant
Dim WS As Worksheet
Dim Dic As New Dictionary
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
Key = WS.Range("A" & i).Value & vbTab & WS.Range("B" & i).Value
If Not Dic.Exists(Key) Then
Dic.Add Key, 1
Else
Dic(Key) = Dic(Key) + 1
End If
Next i
WS.Range("D" & i).Value = Dic(Key)
End Sub
After writing the key range the code will Run.
Now you can see that whatever cell the first and second key is in, it will count duplicates, as in Fion Ryan, the name key is matching, but if the date matches only at one place, it is counting only 2 times.
After this we will again copy the code and paste it at the end of the loop, and we will remove the condition from the second loop which we have pasted then run the code which is the result it will be something like this if the key is 2 times, then count cell will contain 2 whether the key is first or second.
Sub CountByDictionary()
Dim lr As Long, i As Long
Dim Key As Variant
Dim WS As Worksheet
Dim Dic As New Dictionary
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
Key = WS.Range("A" & i).Value & vbTab & WS.Range("B" & i).Value
If Not Dic.Exists(Key) Then
Dic.Add Key, 1
Else
Dic(Key) = Dic(Key) + 1
End If
WS.Range("D" & i).Value = Dic(Key)
Next i
For i = 2 To lr
Key = WS.Range("A" & i).Value & vbTab & WS.Range("B" & i).Value
WS.Range("D" & i).Value = Dic(Key)
Next i
End Sub
Click the Run button.
After running the code, we see that all the keys are only counting their duplicates as many times as the key is.
So, I hope you have understood How to Count Key with Dictionary using Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to Count Key with Dictionary using Excel VBA
