While working with VBA in Excel, we often need to count duplicates in a column in a worksheet. In this article, I’ll show you how you can count duplicates in an Excel column through step-by-step analysis including examples involving a Macro and a User-Defined function with proper illustrations.
Excel VBA to Count Duplicates in a Column (Quick View)
Sub Count_Duplicates_With_Unique_Values() Set Rng = Range("B3:B13") Count = 0 Count2 = 0 Output = "" Match = False Dim Elements() As Variant ReDim Elements(0) Elements(0) = "ExcelDemy" For i = 1 To Rng.Rows.Count For j = LBound(Elements) To UBound(Elements) If Elements(j) = Rng.Cells(i, 1).Value Then Match = True Exit For End If Next j If Match = False Then Count2 = Count2 + 1 ReDim Preserve Elements(Count2) Elements(Count2) = Rng.Cells(i, 1).Value For j = 1 To Rng.Rows.Count If Rng.Cells(j, 1) = Rng.Cells(i, 1) Then Count = Count + 1 End If Next j Output = Output + vbNewLine + Rng.Cells(i, 1) + ": " + Str(Count) Count = 0 End If Match = False Next i MsgBox Output End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
How to Count Duplicates in a Column with Excel VBA (Step by Step Analysis)
Without further delay, let’s move to our main discussion. Here we’ve got a data set with the names of some books.
Let’s count the duplicate values from this data set with the help of Excel VBA.
1. Developing a Macro to Count Duplicates in a Column with Excel VBA Not Ignoring the Unique Values
First of all, we’ll develop a Macro to count the duplicate values not ignoring the unique values. I am showing you the step-by-step procedure to execute this.
⧪ Step 1: Declaring the Range
The 1st step that we need to accomplish is to declare the column as a range whose duplicate values are to be counted. Here it’s B3:B13. The line of code will be:
Set Rng = Range("B3:B13")
⧪ Step 2: Declaring the Other Variables
Then we’ve to declare the other variables. These include two integers, a string, a boolean value, and an array.
The lines of code will be:
Count = 0
Count2 = 0
Output = ""
Match = False
Dim Elements() As Variant
ReDim Elements(0)
Elements(0) = "ExcelDemy"
⧪ Step 3: Starting a For-Loop to Iterate through Each Element of the Range
Next, we’ll start a for-loop to iterate through each element of the declared range.
The line of code will be:
For i = 1 To Rng.Rows.Count
⧪ Step 4: Checking Whether the Duplicates of a Value are Already Counted or Not
Then we’ll iterate through another for-loop to check whether the duplicates of one value are already counted or not.
For j = LBound(Elements) To UBound(Elements)
If Elements(j) = Rng.Cells(i, 1).Value Then
Match = True
Exit For
End If
Next j
⧪ Step 5: Counting the Duplicates of a Value
This is the most important step. If the duplicates of a value aren’t already counted, we’ll iterate through another for-loop to count it duplicates.
If Match = False Then
Count2 = Count2 + 1
ReDim Preserve Elements(Count2)
Elements(Count2) = Rng.Cells(i, 1).Value
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Rng.Cells(i, 1) Then
Count = Count + 1
End If
Next j
Output = Output + vbNewLine + Rng.Cells(i, 1) + ": " + Str(Count)
Count = 0
End If
Match = False
⧪ Step 6: Ending the For-loop and Displaying the Output
Finally, we’ll end the for-loop and display the output.
Next i
MsgBox Output
Therefore, the complete VBA code will be:
⧭ VBA Code:
Sub Count_Duplicates_With_Unique_Values()
Set Rng = Range("B3:B13")
Count = 0
Count2 = 0
Output = ""
Match = False
Dim Elements() As Variant
ReDim Elements(0)
Elements(0) = "ExcelDemy"
For i = 1 To Rng.Rows.Count
For j = LBound(Elements) To UBound(Elements)
If Elements(j) = Rng.Cells(i, 1).Value Then
Match = True
Exit For
End If
Next j
If Match = False Then
Count2 = Count2 + 1
ReDim Preserve Elements(Count2)
Elements(Count2) = Rng.Cells(i, 1).Value
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Rng.Cells(i, 1) Then
Count = Count + 1
End If
Next j
Output = Output + vbNewLine + Rng.Cells(i, 1) + ": " + Str(Count)
Count = 0
End If
Match = False
Next i
MsgBox Output
End Sub
⧭ Output:
Run the code. It’ll display the number of duplicate values of all the elements of column B3:B13.
Read More: How to Count Duplicates in Column in Excel (3 Ways)
2. Creating a Macro to Count Duplicates in a Column with Excel VBA Ignoring the Unique Values
This time we’ll develop a Macro that’ll display the number of duplicate values of each element of a column ignoring the unique values.
The code is almost the same as the previous one. The only difference is that we’ll display only the values whose number of duplicates is greater than one.
So the VBA code will be:
⧭ VBA Code:
Sub Count_Duplicates_Without_Unique_Values()
Set Rng = Range("B3:B13")
Count = 0
Output = ""
Count2 = 0
Match = False
Dim Elements() As Variant
ReDim Elements(0)
Elements(0) = "ExcelDemy"
For i = 1 To Rng.Rows.Count
For j = LBound(Elements) To UBound(Elements)
If Elements(j) = Rng.Cells(i, 1).Value Then
Match = True
Exit For
End If
Next j
If Match = False Then
Count2 = Count2 + 1
ReDim Preserve Elements(Count2)
Elements(Count2) = Rng.Cells(i, 1).Value
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Rng.Cells(i, 1) Then
Count = Count + 1
End If
Next j
If Count > 1 Then
Output = Output + vbNewLine + Rng.Cells(i, 1) + ": " + Str(Count)
End If
Count = 0
End If
Match = False
Next I
MsgBox Output
End Sub
⧭ Output:
Run the code. It’ll again display the number of the duplicate values of all elements of the range B3:B13, but this time ignoring the unique values.
Read More: How to Ignore Blanks and Count Duplicates in Excel (3 Ways)
Similar Readings:
- Count Duplicates in Excel Pivot Table (2 Easy Ways)
- How to Count Duplicate Values in Multiple Columns in Excel (6 Ways)
- How to Count Duplicates Based on Multiple Criteria in Excel
- VBA to Count Duplicates in Range in Excel (4 Methods)
- Count the Order of Occurrence of Duplicates in Excel (4 Methods)
3. Developing a User-Defined Function to Count Duplicates in a Column with Excel VBA
Finally, we’ll develop a User-Defined function to count the duplicate values of a column with the help of VBA, both ignoring and not ignoring the unique values.
The VBA code will be:
⧭ VBA Code:
Function Count_Duplicates(Rng As Range, Ignore_Unique As Boolean)
Count = 0
Dim Elements() As Variant
ReDim Elements(0)
Elements(0) = "ExcelDemy"
Dim Duplicates() As Variant
ReDim Duplicates(0)
Duplicates(0) = 3
Count2 = 0
Match = False
For i = 1 To Rng.Rows.Count
For j = LBound(Elements) To UBound(Elements)
If Elements(j) = Rng.Cells(i, 1).Value Then
Match = True
Exit For
End If
Next j
If Match = False Then
Count2 = Count2 + 1
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Rng.Cells(i, 1) Then
Count = Count + 1
End If
Next j
ReDim Preserve Elements(Count2)
ReDim Preserve Duplicates(Count2)
Elements(Count2) = Rng.Cells(i, 1).Value
Duplicates(Count2) = Count
Count = 0
End If
Match = False
Next i
Dim Output As Variant
ReDim Output(UBound(Elements) - 1, 1)
If Ignore_Unique = True Then
Count3 = 0
For i = 1 To UBound(Elements)
If Duplicates(i) > 1 Then
Output(Count3, 0) = Elements(i)
Output(Count3, 1) = Duplicates(i)
Count3 = Count3 + 1
End If
Next i
Else
For i = 1 To UBound(Elements)
Output(i - 1, 0) = Elements(i)
Output(i - 1, 1) = Duplicates(i)
Next i
End If
Count_Duplicates = Output
End Function
⧭ Output:
To count duplicates of a column ignoring the unique values, select a range of cells (rows equal to that of the range, and columns 2) and enter this formula:
=Count_Duplicates(B3:B13,TRUE)
Then press CTRL + SHIFT + ENTER (Array Formula). It’ll return an array consisting of the number of duplicates of all the values, ignoring the unique ones.
Similarly, to count not ignoring the unique values, enter:
=Count_Duplicates(B3:B13,FALSE)
Press CTRL + SHIFT + ENTER (Array Formula) and It’ll return an array consisting of the number of duplicates of all the values, not ignoring the unique ones.
Read More: How to Count Duplicates in Two Columns in Excel (8 Methods)
Conclusion
So this is these are the ways to count duplicates in a column with the help of Excel VBA. Hope the examples made everything pretty clear for you. Still, if you have any questions, feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
Your code works great and the explanation of how it works was very helpful for me to modify it for my home project. I modified your “Creating a Macro to Count Duplicates in a Column with Excel VBA Ignoring the Unique Values” and it works fantastic. Now I am trying to modify it again for an issue that came up and I cannot figure it out so I am asking for your guidance. Some cells in my sample data have delimiters between entries (a / in my case) and I am trying to modify your code to remove the delimiter and count the entries separately. For example I have entries:
AA
BB
CC
AA/BB
BB/CC
I would like to split those entries with the delimiter and count them with the individual entries, BB + AA/BB + BB/CC = 3, AA + AA/BB = 2, etc. I know that I need to use the Split function but can’t figure out how to implement it with your code.
Thank you for your hep.
Hello BRIAN,
This is Aung, one of the writers/developers in ExcelDmey. I’ve looked into your matter. I understand you want to split the cell values. Kindly send me your excel file at [email protected]
I’ll make the necessary changes and explain them to you.
Good luck.