This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(92.8, 19%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
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,
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
column B is the answer. i need to use vba to compare value in cell A2 will all value in column A and count number of value the same as cell A2 and report in cell B2. next compare value in cell A3 with all value in column A and count number of value the same as cell A3 and report in cell B3 something like this.
Answer accepted by question author
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
Thank you so much HansV-7609. It easy and work
dear HansV-7609
if i don't need to fix last row. Could you fix this error
Sub count_duplicate_value3()
With Range("b2" & ":b" & Range("b" & Rows.count).End(xlUp).Row)
.Formula = "=COUNTIF(a2:a & range("a" & rows.count).end(xlup).row),a2)"
.Value = .Value
End With
End Sub
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(304, 46%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EZL%3C/text%3E%3C/svg%3E)
DEAR SIR,
Let me ask you alittle bit. I want to increase the count if (A1,B1) value is the same with (A2,B2). Please help me.
A B C
No1 No2 Count
1 1 1
1 1 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
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.
:)
HansV-7609 Great ! It'work. Thank you so much.
