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 · Accepted Answer · 2013-11-28 13:23:01Z

0

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

answered Nov 28, 2013 at 13:23

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Basavaraju B K Over a year ago

Siddharth, that worked excellently!! Just as a single go. Now can u help me on similar kind of problem. Now in the column B instead of count values there are some other numerical values. such for PC-101 there is 20 and again for PC-101 in the next cell there is 30.. something like that. Now I want to add all those cell values for PC-101 and get displayed in one particular cell. Is there any way modifying the same formula so that i can get the result? Please its much immediate..

Siddharth Rout Over a year ago

You can start off with a new question to start with :) This way if I am not online someone else will get an opportunity to help you :)

Peter Albert · Accepted Answer · 2013-11-28 15:13:44Z

0

Assuming that your data in column a is sorted, you can simply place 1 in B2 this formula in B3 and copy it down:

=IF(A2<>A3,B2+1,B2)

:-)

answered Nov 28, 2013 at 15:13

Peter Albert

17.6k6 gold badges68 silver badges89 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related