excel vba:how to count duplicate value

[sompolk]

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,

 

[Fluff]

Hi & welcome to MrExcel.
How aboutin B2 copied down

=COUNTIF(A:A,B2)

 

[sompolk]

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,

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.

 

[Fluff]

Oops the formula should have been A2 not B2

=COUNTIF(A:A,A2)

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to Count Duplicate Value with excel vba - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

Why do you need VBA, when it can be done with a simple formula?

 

[sompolk]

Thank for your suggestion. i need to use VBA because i think it easy for user to check.
this is example file.

Book2.xlsm | Powered by Box

app.box.com

 

[Fluff]

Ok, how about

Sub sompolk()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
   
   Ary = Range("A2", Range("A" & Rows.count).End(xlUp)).Value
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         .Item(Ary(r, 1)) = .Item(Ary(r, 1)) + 1
      Next r
      For r = 1 To UBound(Ary)
         Nary(r, 1) = .Item(Ary(r, 1))
      Next r
   End With
   Range("B2").Resize(UBound(Nary)).Value = Nary
End Sub

 

[sompolk]

Thank you much Fluff. It's work.

 

[Fluff]

You're welcome & thanks for the feedback.