Renaming Sequential Rows to create serial numbers

F

forceten32

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
 
G

Guest

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
 
M

Max

forceten32 said:
.. 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
 
F

forceten32

Excellent, guys.

Thanks.

Fred
Max said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top