Renaming Sequential Rows to create serial numbers
 

I am new to Excel.

I have the following cells in one column in sequential rows:

aas
aas
aas
aas
aas
bbc
bbc
bbc
bbc
bbc


I want to make a command to change these to:

aas001
aas002
aas003
aas004
aas005
bbc101
bbc102
bbc103
bbc104
bbc105


How do I change or rename a series of cells in a column, increasing the
digit by 1 while retaining the three letter prefix? I'm trying to create
product serial numbers in a worksheet with hundreds of rows of products.

Thanks,

Fred

Renaming Sequential Rows to create serial numbers
 

I assumed your codes starting with a added a 101, 102, etc, and those
starting with b got a 201, 202, etc.

I put a table in f5:g6 with a in f5, b in f6, and 1 in g5, 2 in g6

I then listed your 3 letter codes in a4 and down, and put the following in
b4 and down. It takes the code in a4, and if the first letter changes (say a
to b) it looks up the new first letter and adds the corresponding digit, and
a "01". if the first letter does not change, it incrments the digits by 1

=A4&VLOOKUP(LEFT(A4,1),$F$5:$G$6,2)&IF(LEFT(A4,1)< LEFT(A3,1),"01",IF(LEFT(RIGHT(B3,2),1)="0",0,"")& RIGHT(B3,2)+1)

not sure if this is what you are looking for

"forceten32" wrote:

I am new to Excel.

I have the following cells in one column in sequential rows:

aas
aas
aas
aas
aas
bbc
bbc
bbc
bbc
bbc


I want to make a command to change these to:

aas001
aas002
aas003
aas004
aas005
bbc101
bbc102
bbc103
bbc104
bbc105


How do I change or rename a series of cells in a column, increasing the
digit by 1 while retaining the three letter prefix? I'm trying to create
product serial numbers in a worksheet with hundreds of rows of products.

Thanks,

Fred

Renaming Sequential Rows to create serial numbers
 

"forceten32" wrote:
.. How do I change or rename a series of cells in a column, increasing the
digit by 1 while retaining the three letter prefix? I'm trying to create
product serial numbers in a worksheet with hundreds of rows of products.

One way which might suffice for the intent indicated ..

Assuming data in A1 down
Put in B1, copy down:
=A1&TEXT(COUNTIF($A$1:A1,A1),"000")

This would yield in col B, for the sample data posted:

aas001
aas002
aas003
aas004
aas005
bbc001
bbc002
bbc003
bbc004
bbc005
etc

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Renaming Sequential Rows to create serial numbers
 

Excellent, guys.

Thanks.

Fred
"Max" wrote in message
...
"forceten32" wrote:
.. How do I change or rename a series of cells in a column, increasing
the
digit by 1 while retaining the three letter prefix? I'm trying to create
product serial numbers in a worksheet with hundreds of rows of products.

One way which might suffice for the intent indicated ..

Assuming data in A1 down
Put in B1, copy down:
=A1&TEXT(COUNTIF($A$1:A1,A1),"000")

This would yield in col B, for the sample data posted:

aas001
aas002
aas003
aas004
aas005
bbc001
bbc002
bbc003
bbc004
bbc005
etc

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Renaming Sequential Rows to create serial numbers
 

You're welcome, Fred
Thanks for the feedback
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"forceten32" wrote in message
ink.net...
Excellent, guys.

Thanks.

Fred