hOW DO I
 

I have a spreadsheet that has several columns that contai customer account
numbers. Currently the name and customer nubers are laid out like this

Acount Name
albfre Albert Frey
I want to change to this convention alb101 as the customer account number
Then is another customer come with the same beginning to their name I will
and 102 and so on like this

alb101 Albert frey
alb102 Albert Pters
alb103 Alberto Jaun

I have a colum with the first three characters from the first name ie Alb
I created a column with the 101 in it.
I have a column with the two combined Ie Alb101

My problem I have dupilcates in the last column and I may not have that.
Wondering if there is a way to populated another column that would look at my
first combined column ie alb101 and if the cell above it is the exact same
the add 1 to the current cell.

example
A B
1 alb101 this one to stay the same
2 alb101 change this one to alb102
3 alb101 change this one to alb103

and so on only changing if the first three characters are the as os that
alc101 would be the next if the next name was alcoa Steel

for your example Assuming you won't have more than 99 duplicates and you have
sorted column A
in B1
= left(A2,Len(A2)-2)
copy down to bottom of data
in C1
=A1
in C2
=B2&Text(Countif($B$1:B2,B2),"00))
and copy down to bottom of data
(Sorry I used 3 columns, not 2)
"Darrell Martin" wrote:

I have a spreadsheet that has several columns that contai customer account
numbers. Currently the name and customer nubers are laid out like this

Acount Name
albfre Albert Frey
I want to change to this convention alb101 as the customer account number
Then is another customer come with the same beginning to their name I will
and 102 and so on like this

alb101 Albert frey
alb102 Albert Pters
alb103 Alberto Jaun

I have a colum with the first three characters from the first name ie Alb
I created a column with the 101 in it.
I have a column with the two combined Ie Alb101

My problem I have dupilcates in the last column and I may not have that.
Wondering if there is a way to populated another column that would look at my
first combined column ie alb101 and if the cell above it is the exact same
the add 1 to the current cell.

example
A B
1 alb101 this one to stay the same
2 alb101 change this one to alb102
3 alb101 change this one to alb103

and so on only changing if the first three characters are the as os that
alc101 would be the next if the next name was alcoa Steel