Auto generate number with text string

[hkbhansali]

I am trying to auto generate numbers for a group of records that follows a sequence,

ie:

"A" "B"
1 LM/A/001 Abhay
2 LM/B/001 Bharat
3 LM/C/001 Chandresh
4 LM/A/002 Atul
5 LM/B/002 Bhavesh

(in column"A"-A-B-c-is a first letter of name indicate in "B"),

i want when i input name in cell "B6" excel automatic generate in cell "A6" No.with Name first Character
ie: in "B6" i input "Champak" -excel automatic generate in cell "A6" "LM/C/2"

any assistance would be appreciated.

[jindon]

A1:

=IF(B1="","","LM/"&LEFT(B1,1)&"/"&TEXT(COUNTIF(B$1:B1,LEFT(B1,1)&"*"),"000"))

and filldown

[hkbhansali]

Hi Jindon,
Thanks for prompt reply
its work
So kind of you!
Thanks again

[hkbhansali]

Hi Jindon,
can you provide vb code instead of formula?

[jindon]

Please Login or Register  to view this content.

[hkbhansali]

Thanks Jindon,
Perfect...