-1

How can I count duplicate data in a column using excel?

Example

  A     B
apple   1
apple   2
apple   3
ball    1
bat     1
dog     1
dog     2
gole    1
gole    2
gole    3
gole    4
bmargulies
  • 97,814
  • 39
  • 186
  • 310
  • possible duplicate of [Counting duplicates in Excel](http://stackoverflow.com/questions/6875479/counting-duplicates-in-excel) – Amos M. Carpenter Apr 30 '15 at 07:40
  • please check the example correctly that is not the answer –  Apr 30 '15 at 07:46
  • i got the answer its =IF(A2=A1,B1+1,1) –  Apr 30 '15 at 07:52
  • Glad you got it working for your example. You could've also adapted the accepted answer to the question I considered a duplicate, though, and simply used `=COUNTIF(A$1:A1, A1)`, `=COUNTIF(A$1:A2, A1)`, etc. – Amos M. Carpenter Apr 30 '15 at 07:58

1 Answers1

2

I think there is no uniform formula for all cells in your case.

I would suggest something like this: Put this on B column and then fill down the rest.

=COUNTIF($A$1:$A1,$A1)

After you fill the other rows, values of rows would look like this

B1=COUNTIF($A$1:$A1,$A1)
B2=COUNTIF($A$1:$A2,$A2)
B3=COUNTIF($A$1:$A3,$A3)
Apple
  • 183
  • 7