Excel Sequence number based on cell value

Question

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.

I think you just need to shorten the formula supplied in the answers already - in A1 use =COUNTIF(C1:$C$1,C1). I assume your note that in column C i have multiple data need to select means you want a running count of each item in column C? — Darren Bartrup-Cook
I want to set selection based on criteria In column C, eg. Billy. Is there anyway i can select all? any wildcard i can use? — robin

Gowtham Shiva Gowtham Shiva · Accepted Answer · 2017-09-20T15:41:29

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.

Excel Sequence number based on cell value

2 Answers