find duplicate-create new column-write number coding

[elba7312]

Hi all,
Please I would like you to help me in creating a VBA code for:
1- find duplication in column B (file is attached)


-- removed inline image ---
[/INDENT]Thanks a lot for help

 

[dave3009]

You could do this without VBA by using a formula like this

=COUNTIF($B:$B, B2)&COUNTIF($B$2:B2, B2)

 

[p45cal]

dave3009 beat me to it with the formula, you can make it into a macro:

Sub blah()
With Range(Range("B2"), Range("B2").End(xlDown)).Offset(, 1)
  .FormulaR1C1 = "=COUNTIF(C2,RC2) & COUNTIF(R2C2:RC2,RC2)"
.Value = .Value
End With
End Sub

I wonder whether you'll be able to interpret what's happening when you get above 10 instances, perhaps add a space:

Sub blah2()
With Range(Range("B2"), Range("B2").End(xlDown)).Offset(, 1)
    .FormulaR1C1 = "=COUNTIF(C2,RC2) & "" "" & COUNTIF(R2C2:RC2,RC2)"
.Value = .Value
End With
End Sub

or formula:
=COUNTIF($B:$B,$B2) & " " & COUNTIF($B$2:$B2,$B2)
copied down from C2.

 

[elba7312]

They worked fine. Thank you all