• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Easy Way to Generate System Names

S

Simon Patenaude

New Member
Hi All,

This is my first post so apologies for any rooky mistakes.

I am a Live TV Truck Designer and I am constantly creating documents with signal names with sources and destinations. The documents I create will have the system or engineering names for signals.

For example I could have 20 cameras which would be labeled CAM-1, CAM-2… CAM-19, CAM-20. Cameras are easy enough in that cell A1 I can type CAM-1 drag the corner and all cells will increment nicely. That said, most equipment have dual channels so I would have: “CARD-1-A, CARD-1-B, CARD-2-A, CARD-2-B”… and so on. Audio equipment like CD players have similar system names i.e “CD-1-L, CD-1-R”. These system names can have up to 8 channel per piece of gear such as “MV-1-A, MV-1-B… MV-1-G, MV-1-H”.

Anything like cameras, I can easily drag and excel does the work for me. As soon as I get into multi-channel devices Excel tells me to take a hike. The workaround I have been doing is to drag these labels and rename each one individually. This is okay for tweaking a few names here and there, but when I start a new project where I may have close to 2000 lines to edit, it is a tedious and long process. I have been doing some research for a few months on finding a solution that would allow me to quickly generate these lists by dragging cells, but I haven’t found a solution I can easily understand or implement for my co-workers as well.

Formulas sort of helped, but I found them “clunky” for what I was trying to achieve. I’m hoping there is something like custom formatting that would allow me to say: “Excel dude, I have 3 CARDS each with 8 outputs. Each CARD shall be labelled 1,2,3 each output shall be labeled A,B,C…” The ultimate would be have a way to customize this on the fly. When I’m done with CARD I want to generate 4 CD players each with a Left and a Right output.

I know the answer might already be in Chandoo’s amazing library, but maybe because I don’t know what I am looking for I have not found the solution I need. Could someone offer me a couple suggestions on what articles might give me an answer? I hope this question makes sense.

Thank you
Simon
 
Hi Simon,

See the attached file. It has formula solution. Yellow cells are input cells. Green is helper and orange is solution.

Regards,
 

Attachments

  • sIMON_CHANDOO.xlsx
    9.9 KB · Views: 5
Hi Somendra,

Thank you so much for taking the time to look at my question and to create a solution! That said, I found that using formulas for this does not capture the efficiency I'm after.

The solution I'm hoping for is a way to define a label. Custom formatting is the closest I could find to what I want to achieve but falls short (possibly because i do not fully understand how they work). Could I define a cell format to be XXXX-YY-ZZ where X is a max 4 character label such as VTR, Y is a 2 digit number from 1 to 99, Z I would like to define to be numbers or letters?

Another solution that may work is a way for me to define a list of equipment that excel can reference. Much like when I write "January" in cell A1, Excel then knows to automatically fill in "Frebruary", "March" as I drag the corner.

Attached is one sample list that I work with. Columns B and C are the labels I created. This is a segment of a list with 2580 lines.
 

Attachments

  • Audio Router Example.xlsx
    27.5 KB · Views: 8
Hi Simon ,

VBA would make it possible.

You could have a generalized procedure , which could take a template , a mask and number of repetitions as parameters , and then generate the output.

For example , suppose the template were :

GPO OUT 1

The mask could be :

XXXXXXXX

which means these eight characters from the template would remain unchanged ; thus the 9th character alone would be subject to change.

The code could be written so that it detects whether the characters subject to change are numeric or alphabetic or a combination , and then increment the changeable characters to generate the output.

If you want to make it even more general in nature , you could even specify the increment ; thus an increment of 1 would increment in steps of 1 ; thus starting from 1 , you would get outputs of 2 , 3 , 4 ,... , while starting from H , you would get outputs of I , J , K , L ,...

An increment of 2 with the above starting points would result in outputs of 3 , 5 , 7 ,... and J , L , N ,....

Writing the code and testing it would take some time , and a lot of input data from your end , but if your activity is a recurring activity , it might help.

Narayan
 
Hi Narayan,

What you are describing sounds exactly like what i need. That said, my knowledge of VBA is basic at this point. I am slowly learning by reading forms and creating practice codes during down time, but what you describe above sounds fairly advanced.

How can we start? Is there literature you can point me to that would get me started in the right path?
 
You must log in or register to reply here.
Back
Top