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:

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


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.