Excel VBA: How to Count Duplicate Value

Question

Column A have many ID, there are duplicate value such as cell(A2) ID 300502489400, cell(A3) ID 300502520900, cell(A4) ID 300502520900, cell(A5) ID 300502520900, cell(A6) ID 300502523900, cell(A7) ID 300502520900,

i need to count number of duplicate and report in Column B result such as cell(B2) =1, cell(B3) =4, cell(B4) =4, cell(B5) =4, cell(B6) =1, cell(B7) =1,

Answer 1

You don't need VBA for that In B2:

=COUNTIF($A$2:$A$22,A22)

Fill down.

If you really want to use VBA:

Sub Test()
    With Range("B2:B22")
        .Formula = "=COUNTIF($A$2:$A$22,A2)"
        .Value = .Value
    End With
End Sub

Answer 2

Try this:

Sub count_duplicate_value3()
    Dim m As Long
    m = Range("A" & Rows.Count).End(xlUp).Row
    With Range("B2:B" & m)
        .Formula = "=COUNTIF($A$2:$A$" & m & ",A2)"
        .Value = .Value
    End With
End Sub

If you don't want a value in the last used row, change the line that sets m to

    m = Range("A" & Rows.Count).End(xlUp).Row - 1

Answer 3

HansV-7609 Great ! It'work. Thank you so much.

Answer 4

Hi @polars_k ,
Thanks for "Accept Answer" answer of HansV, I also suggest you upvote it. Other partners who read the forums with the same issue can get more information from the correct result.

Thank you.
:)