Excel VBA to Count Duplicates in a Column (A Complete Analysis)

Get FREE Advanced Excel Exercises with Solutions!

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: A Complete Analysis

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 VBA Code to Count Duplicates in a Column in Excel

Excel VBA to Count Duplicates in a Column: A Complete Analysis

Without further delay, let’s move to our main discussion. Here we’ve got a data set with the names of some books.

Data Set to Count Duplicates in a Column with Excel VBA

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

VBA Code to Count Duplicates in a Column in Excel

⧭ Output:

Run the code. It’ll display the number of duplicate values of all the elements of column B3:B13.

Read More: How to Ignore Blanks and Count Duplicates in Excel


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

VBA Code to Count Duplicates in a Column with Excel VBA

⧭ 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.

Output to Count Duplicates in a Column with Excel VBA

Read More: How to Use COUNTIF Formula to Find Duplicates


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)

Entering Formula to Count Duplicates in a Column with Excel VBA

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.

Formula to Count Duplicates in a Column with Excel VBA

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 Duplicate Values Only Once in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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.


Related Articles


<< Go Back to Count Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo