CODEWITHSUNDEEP
excelvba
robin
robin

Reputation: 181

Excel Sequence number based on cell value

In excel, how can I create auto numbering in column A ? eg. If I key in cell value in D1 with "BILLY", column A will populate sequence numbers from 1,2,3.....

I have googled and found the closet is using below formula but unable to accomplish what i want because in column C i have multiple data need to select.

https://superuser.com/questions/645859/auto-sequential-numbering-based-on-cell-criteria/645903

A     B     C
     ABC    JAMES
     XYZ    BOB
1    OXY    BILLY
2    BNX    BILLY
     SDA    MIKE
3    WXK    BILLY
     SAK    JANE

Anyone have done this before with formula or vba? would appreciate your help, thanks.

Upvotes: 1

Views: 4290

Answers (2)

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

Select the whole column A or just the cells that you want to fill in and enter the formula in the formula bar,

=IF(C1=D$1,COUNTIF(C$1:C1,D$1),"")

Press ctrl+enter to fill the formula for the range of selected cells.

You could then change the value in cell D1 and see the output.

enter image description here

enter image description here

Upvotes: 2

Vincent G
Vincent G

Reputation: 3188

Put the formula =IF(C1=$D$1,COUNTIF($C$1:C1,$D$1),"") in A1 and copied down.

Upvotes: 2

Related Questions