Find the next available username number?

[danbates]

Hi,

I was wondering if there is a way of finding the next available username number from a list of usernames?

If the list below is in column A, I would like to enter the initials in another cell, say D10.

So when I enter DB in cell D10, a formula or VBA code then tells me in cell E10 that the next username for someone with the initials of DB is DB4.

DB1

AS1

AL1

DB2

PD1

AA1

DB3

AL2

I hope this kind of makes sense and I'd really appreciate it if someone could help me.

Thanks
Dan

 

[Sufiyan97]

Try with helper columns

Book9
	A	B	C	D	E	F
1	Name	User Name			Next User Name
2	DB	DB1		DB	DB4
3	AS	AS1		AS	AS2
4	AL	AL1		AL	AL3
5	DB	DB2		DB	DB4
6	PD	PD1		PD	PD2
7	AA	AA1		AA	AA2
8	DB	DB3		DB	DB4
9	AL	AL2		AL	AL3
10
11
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=A2&COUNTIF($A$2:A2,A2)
E2:E9	E2	=D2&COUNTIF(A:A,D2)+1

 

[danbates]

Perfect, thank you.

 

[Sufiyan97]

You're welcome.