Comparing the cell values and printing the count in Excel using a formula or function?

Question

I need a formula or function which is going to fulfill my below mentioned need. I have a excel data of around 11000 rows and data looks somewhat like in Column A: Now in column B i want the result to be printed like it mentioned below: which literally means it should count the values present in column A and print it in the column B, I don't need to repeat count:

Column A  Column B
PC-101      1
PC-101      1
PC-102      2
PC-102      2
PC-103      3
PC-104      4
PC-106      5
PC-107      6
PC-104      4
PC-106      5
PC-106      5

I tried with the "count" series formulas but the result was null. Even i wrote the macro as given below( which i got from stackoverflow) but even it is printing the repeating count:

Sub CountOccurence()
' Reference: Microsoft Scripting Runtime

Application.ScreenUpdating = False

Set oDict = New Dictionary
Dim wS As Worksheet
Dim r As Integer, rLast As Integer

    Set wS = Sheet1
    rLast = wS.Cells(1, 1).CurrentRegion.Rows.Count
    For r = 3 To rLast Step 1
        If Not (oDict.Exists(wS.Cells(r, 1).Value)) Then
            oDict.Add wS.Cells(r, 1).Value, 1
        Else
            oDict.Item(wS.Cells(r, 1).Value) = oDict.Item(wS.Cells(r, 1).Value) + 1
        End If
        wS.Cells(r, 2).Value = oDict.Item(wS.Cells(r, 1).Value)
    Next r
Set oDict = Nothing
Application.ScreenUpdating = True
End Sub

Can anyone help me regarding this? Thanks in advance.

Siddharth Rout Siddharth Rout · Accepted Answer · 2013-11-28T13:23:01

I tried with the "count" series formulas but the result was null.

A simple Excel formula can do this.

Put 1 in Cell B1 and then put this formula in cell B2 and pull it down.

=IF(COUNTIF($A$1:$A2,A2)>1,VLOOKUP(A2,A:B,2,0),B1+1)

enter image description here

Comparing the cell values and printing the count in Excel using a formula or function?

2 Answers