Auto populate numbering sequence

[newexcler]

Hi Experts
Please can you let me know how to accomplish auto numbering populated ?
I can use the formula
Concatenate with . =IF(A2<>A1,1,B1+1) but unfortunately the list is not ordered based on ID I am considering. Any help? There are 100s of rows and this needs to be auto populated with protection

Input Column Expected Output

Apple Apple.1
Orange Orange.1
Apple Apple.1
Apple Apple.2
Grapes Grapes.1
Water Melon Water Melon.1
Rock Melon Rock Melon.1
Guava Guava.1
Apple Apple.3
Orange Orange.2
Water Melon Water Melon.2
Rock Melon Rock Melon.2
Orange Orange.3

help is appreciated

 

[mrshl9898]

=A2&"."&COUNTIF(A$2:A2,A2)

 

[newexcler]

Oh thank you a ton, it worked, I was about to edit with my mistake in Apple field and you are so awesome. Thanks a ton. So Can I use this in data validation in custom and to ensure no manual typing? or best way is to lock the cell as I see the steps in internet, please advise

 

[mrshl9898]

You could turn the data set into a table, select a cell and press ctrl + T. As new data is added in column A the formula will auto populate.

Then just lock the column

 

[newexcler]

Thanks a ton.

 

[newexcler]

@mrshl9898 How to mark this question as answered? I think I forgot.

 

[Peter_SSs]

How to mark this question as answered?

A comment like this is sufficient for that.