how do I serially append numbers within a cel lwithout changing the contents of each cell

[juleze]

Please, I will like to know how to serially append numbers within a cell.

For example:
I'm trying to create codes to link to assets in a hotel. So, I go like this

RM101/BED/001
RM102/BED/002
RM103/BED/003
RM104/BED/004
RM105/BED/
RM106/BED/

Now, I could have dragged it down to number it serially, but that will change the Room numbers (RM 101, RM 102, RM103...).
So, I'll like to know the best way I can number the rooms serially without changing the room numbers.
Thanks a lot.

 

[juleze]

Maybe it is just me, but I am having trouble deciphering exactly what your question is. I sounds like you have a list of room numbers and you want to affix serial numbers to them, but it is not clear whether the serial numbers are on a per room basis or independent of the room numbers. Can you post a clear sample of your list of values in the cell and then a second list of what that first list should look like after the serial numbers are applied?

Okay.
This is what's happening:
I'm creating a fixed asset register and I have to attach a code to each asset.
The asset codes should look like this:

LL/FF/RM101/BED/001
LL/FF/RM102/BED/002
LL/FF/RM103/BED/003
LL/FF/RM104/BED/004

Another asset would be like this

LL/FF/RM101/MRR/001
LL/FF/RM102/MRR/002
LL/FF/RM103/MRR/003
LL/FF/RM104/MRR/004

Now, I already have this:


LL/FF/RM101/BED/001
LL/FF/RM102/BED/
LL/FF/RM103/BED/
LL/FF/RM104/BED/

LL/FF/RM101/MRR/001
LL/FF/RM102/MRR/
LL/FF/RM103/MRR/
LL/FF/RM104/MRR/

I want to then serially attach numbers on the right like this:
LL/FF/RM101/MRR/001
LL/FF/RM102/MRR/002
LL/FF/RM103/MRR/003
LL/FF/RM104/MRR/004

 

[Rick Rothstein]

Okay.
This is what's happening:
I'm creating a fixed asset register and I have to attach a code to each asset.
The asset codes should look like this:

LL/FF/RM101/BED/001
LL/FF/RM102/BED/002
LL/FF/RM103/BED/003
LL/FF/RM104/BED/004

Another asset would be like this

LL/FF/RM101/MRR/001
LL/FF/RM102/MRR/002
LL/FF/RM103/MRR/003
LL/FF/RM104/MRR/004

Now, I already have this:


LL/FF/RM101/BED/001
LL/FF/RM102/BED/
LL/FF/RM103/BED/
LL/FF/RM104/BED/

LL/FF/RM101/MRR/001
LL/FF/RM102/MRR/
LL/FF/RM103/MRR/
LL/FF/RM104/MRR/

I want to then serially attach numbers on the right like this:
LL/FF/RM101/MRR/001
LL/FF/RM102/MRR/002
LL/FF/RM103/MRR/003
LL/FF/RM104/MRR/004

I can give you a VB solution which will make this easy for you, but I still need to understand your layout a little more. It sounds like you have within the same sheet groups of data that you want to serialize independent from other groups of data that have already been serialized or are yet to be serialized... is that correct? In other words, you may have one group of data that have 001, 002, etc. affixed to them and somewhere else on that sheet will be another group of data that will have 001, 002, etc. affixed to it as well, correct? If so, how are the groups of data to be identified... can you easily select them or will there be too many cells to do that conveniently? Would typing the address range be something you could do? Or better still, are the groups of data segmented from each other, perhaps with one or more blank rows and/or columns between each group? Anything you can tell me about the layout of your data and the structure of the worksheet would be helpful in deciding on how to write a macro for you.

 

[juleze]

Here is a live example:

ASSET		ASSET CODE				LOCATION		NUMBER OF ASSETS
JACUZZI		LL/FF/RM301/JACZ/001				ROOM 301		1
JACUZZI		LL/FF/RM302/JACZ/002				ROOM 302		1
JACUZZI		LL/FF/RM303/JACZ/003				ROOM 303		1
JACUZZI		LL/FF/RM304/JACZ/004				ROOM 304		1
JACUZZI		LL/FF/RM305/JACZ/005				ROOM 305		1
JACUZZI		LL/FF/RM306/JACZ/006				ROOM 306		1
JACUZZI		LL/FF/RM307/JACZ/007				ROOM 307		1
JACUZZI		LL/FF/RM308/JACZ/008				ROOM 308		1
COUCH		OHL/FF/FDSK/COU/001				FRONT DESK	2
COUCH		LL/FF/RM301/COU/002				ROOM 301		1
COUCH		LL/FF/RM302/COU/003				ROOM 302		1
COUCH		LL/FF/RM303/COU/004				ROOM 303		1
COUCH		LL/FF/RM304/COU/005				ROOM 304		1
COUCH		LL/FF/RM305/COU/005				ROOM 305		1
COUCH		LL/FF/RM306/COU/006				ROOM 306		1
COUCH		LL/FF/RM307/COU/007				ROOM 307		1
COUCH		LL/FF/RM308/COU/008				ROOM 308		1
COUCH		LL/FF/RM309/COU/009				ROOM 309		1
COUCH		LL/FF/RM310/COU/010				ROOM 310		1
COUCH		LL/FF/RM311/COU/011				ROOM 311		1
COUCH		LL/FF/RM312/COU/012				ROOM 312		1
COUCH		LL/FF/RM314/COU/013				ROOM 314		1
COUCH		LL/FF/RM317/COU/014				ROOM 317		1
COUCH		LL/FF/RM318/COU/015				ROOM 318		1
COUCH		LL/FF/RM321/COU/016				ROOM 321		1
COUCH		LL/FF/RM321/COU/017-018				ROOM 321		2
COUCH		LL/FF/RM322/COU/019				ROOM 322		1
COUCH		LL/FF/RM322/COU/020-021				ROOM 322		2

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Does this answer your questions, please?

N.B: I just realised that I made an error in the first code for "couch" as there are 2 couches at the Front Desk ( So, it should have been LL/FF/FDSK/001-002 instead of what's written there now "LL/FF/FDSK/001"). This means I would need to start the codes for couch afresh.

 

[Rick Rothstein]

Does this answer your questions, please?

N.B: I just realised that I made an error in the first code for "couch" as there are 2 couches at the Front Desk ( So, it should have been LL/FF/FDSK/001-002 instead of what's written there now "LL/FF/FDSK/001"). This means I would need to start the codes for couch afresh.

That answer the bulk of the questions I had, plus it give us additional, critical information that you left out of your original question... the fact that there is a "Number Of" column which affects the numbering. Any solution we would have given you without know that would have had to been thrown out in order to create a new solution that accounted for it... that would have been a time waster, so I am glad you posted what you did in your last message.

Okay, two more question and I think we will be good to go...

1) You show a blank row between your JACUZZI and COUCH groups, is there always a blank row between asset groups? If so, that will make the coding much easier, but if not, I can work around it as well... I just need to know which it is.

2) What column letter are each of the columns you showed us in?

 

[juleze]

That answer the bulk of the questions I had, plus it give us additional, critical information that you left out of your original question... the fact that there is a "Number Of" column which affects the numbering. Any solution we would have given you without know that would have had to been thrown out in order to create a new solution that accounted for it... that would have been a time waster, so I am glad you posted what you did in your last message.

Okay, two more question and I think we will be good to go...

1) You show a blank row between your JACUZZI and COUCH groups, is there always a blank row between asset groups? If so, that will make the coding much easier, but if not, I can work around it as well... I just need to know which it is.

2) What column letter are each of the columns you showed us in?

Good evening, sir.
I'm sorry for responding this late. Was swamped at work.

1. There are no blank rows between the assets.
2. The asset names are in the C column, while the Asset codes are in the E column.

Thanks.