Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel - alphanumeric sequencing


(!)

freekrob's Avatar
freekrob freekrob is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
22-Jan-2009, 04:50 PM #1
Excel - alphanumeric sequencing
I am working on a project to make small labels in a sequential order which allows use to print an excess at a time (50-100+).

Basically, the numbering system is alphanumeric and the label it self has certain characteristics.

the numbers may be listed as XXX01, XXX02... etc.

How I originally did it was to add a custom cell format such as
"[Label]: XXX"general
which would allow me to fill in one number and sequence it down through the follow cells using the simple formula =A2+1 and placing whatever number I wanted it A2. That allowed me to have what I wanted.

The problem with this is that it's too complicated for others in the office to figure out and all custome cell formats would have to be changed individually whenever XXX changed to YYY or whatever.

Additionally, I added another cell into the label for more information which has worked out fine, but I figured everyone likes to know as much as possible. So basically:

A1: Header (unchanged and used on every page, I need no help here)A2: [Label]: XXX100 (A2 and A3 are one label; I want to make XXX100 the first alphanumeric number)A3: [Label 2]: AAA (A3 uses custom cell formating as "[Label 2]"@ so we can change AAA at any time)A4: [Label: XXX101 (A4 and A5 are a second label; A4 would read from A2 as A2+1 (XXX101))A5: [Label 2]: AAA (A5 reads from A3 as =A3 and also uses the same custom cell formating)


So anyway, I didn't know if someone knew a formulla or module I could us for this stuff. Please let me know and I really appreciate your help.

Robert
freekrob's Avatar
freekrob freekrob is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
22-Jan-2009, 04:53 PM #2
The above should read as

A1: Header
A2: [Label]: XXX100
A3: [Label 2]: AAA
A4: [Label]: XXX101
A5: [Label 2]: AAA

Sorry for the messiness.
turbodante's Avatar
turbodante turbodante is offline
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
22-Jan-2009, 06:36 PM #3
Can you post up a sample file to illustarte what you are trying to achieve?
freekrob's Avatar
freekrob freekrob is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
23-Jan-2009, 09:02 AM #4


Hopefully we can see the above image of what I'm trying to do. Like I said, A1 is set. A2 works with a custom cell format right now as seen in the right side of the screenshot. The numbers I am using are alphanumeric, that being XXX100. Right now all of the similaur cells (A2, A4, A6 etc.) are using the same custom cell formatting ("[Label]: XXX"General), and cells A4, A6, etc are set with =A2+1, =A4+1 etc, respectively. This will get difficult because if I need to change the aplha part of it (XXX) I will have to go to every cell and change the custom cell formatting (to something like "[Label]: YYY"General).

So what I'm looking for, in a nut shell, is a way to simply set my custom cell formatting to "[Label]: "General, and then input a singel aplhanumeric number into cell A2, then to have a formula in the following like cells (A4, A6, etc.) that is =A2+1. This makes it so once i put my number in A2, all of the other like cells change automatically.

So, since I"m not sure how to make aplhanumeric numbers count, I'm stuck.

In the end, the label would looke something like:

[Header]
[Label]: XXX100
[Label 2]: AAA

[Header]
[Label]: XXX101
[Label 2]: AAA

[Header]
[Label]: XXX102
[Label 2]: AAA

The only properties that will change would be XXX100, set to count from cell to cell, and AAA, which is already easily done through a formula of cell2=cell1.

Does that make anymore sense?
turbodante's Avatar
turbodante turbodante is offline
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
23-Jan-2009, 10:24 AM #5
I see. But I can;t see a way of doing this without the use of a helper column; to contain first of all the text '[Label ]:xxx' and then the numbers 100 to n.

Else, the otherway I can think to do this would be with VBA. Let me know if you need further elaboration with either suggestion.
freekrob's Avatar
freekrob freekrob is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
23-Jan-2009, 12:41 PM #6
I had thought about a help column actually but have not tried it yet. I'm unsure how it will all work out on the label we're using (apx 1.5" by 2"). I actually assumed VBA would have to be used. That's an aspect of excel I never really use so I wouldn't know where to begin. I have looked up some info but getting it under way could prove difficult for me.
turbodante's Avatar
turbodante turbodante is offline
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
23-Jan-2009, 06:35 PM #7
Quote:
Originally Posted by freekrob View Post
I had thought about a help column actually but have not tried it yet. I'm unsure how it will all work out on the label we're using (apx 1.5" by 2").
Have uploaded a sample on how this could work. You could always colour the helper column, in white - or hide the column so it does not print.

Quote:
I actually assumed VBA would have to be used. That's an aspect of excel I never really use so I wouldn't know where to begin. I have looked up some info but getting it under way could prove difficult for me.
If you'd still like to go this route, let me know and I'll see what I can do. Welcome to the forum btw.
Attached Files
File Type: xls AlphaNumericSeq.xls (19.0 KB, 188 views)
freekrob's Avatar
freekrob freekrob is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
26-Jan-2009, 11:27 AM #8
Well, I worked with going with the helper column a bit but I haven't gotten the chance to test print yet. On the old sheet that I had, it seemed to have some sort of permanent print area where only those cells were shown and every other cell in the worksheet was greyed out. You could put data in them but it would never print. I haven't been able to find that setting anywhere.

Anywho, I would like to try the VBA route as well to get the best looking label I can for professionalism. If you could give me some pointers are codes or what have you, I would appreciate it. Like I said, I really know nothing about creating and using modules and I sure don't know Visual Basic programming code well enough.
turbodante's Avatar
turbodante turbodante is offline
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
26-Jan-2009, 05:22 PM #9
Quote:
Originally Posted by freekrob View Post
Well, I worked with going with the helper column a bit but I haven't gotten the chance to test print yet. On the old sheet that I had, it seemed to have some sort of permanent print area where only those cells were shown and every other cell in the worksheet was greyed out. You could put data in them but it would never print. I haven't been able to find that setting anywhere.

You can set up what you called a permanent print area using one of Excel's viewing modes - Page Break preview. You can set the print areas accordingly. (See attached file.)

Quote:

Anywho, I would like to try the VBA route as well to get the best looking label I can for professionalism. If you could give me some pointers are codes or what have you, I would appreciate it. Like I said, I really know nothing about creating and using modules and I sure don't know Visual Basic programming code well enough.
VBA is really just a step up from playing around with Excel's formulas. There's no need tp be worried about modules and wotnots, they're just a place that hold your code, just like sheets are a place to hold your numbers/data.

The best way to learn, is to first have a project in mind - as you have here. The way that I learned was to record a few macros to achive your objects and then examin the code in the VBA editor.

For instance, you could start by recoding a macro of entering '[Label]XXX100' into a cell and see how the code works. Next, I'd have a look at example code prodived in Excel's own help for looping to provide you some sort of automation to loop from 100 to n, to bolt onto the [Label]xxx part of the string.
Attached Files
File Type: xls AlphaNumericSeq02.xls (19.0 KB, 108 views)
freekrob's Avatar
freekrob freekrob is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
27-Jan-2009, 03:39 PM #10
Ok, I've got the page break preview thing. Thanks for that.

I've been working with VBA as you suggested, recording sessions and checking out the code. I'm pretty much still running into the same issue, that being that I can't seem to get it to actually count the alphanumeric number. I'd like to, ideally, have myself or any one in the office be able to simply type AAA100 into the first sell and have the other cells count from that (AAA101, AAA102... etc.), just as if we were only entering 100 to cell A2 and setting cell A3 as =A2+1. It needs to be that way because AAA will need to change to any of a number of other sequences (BBB, CCC, THW, whatever). Everything else seems like it's working out. I'm continuing my work to figure this out. I haven't found any where else in my search thus far that explains how I can do this. Thanks for your help though!
turbodante's Avatar
turbodante turbodante is offline
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
28-Jan-2009, 06:07 AM #11
Quote:
Originally Posted by freekrob View Post
Ok, I've got the page break preview thing. Thanks for that.

I've been working with VBA as you suggested, recording sessions and checking out the code. I'm pretty much still running into the same issue, that being that I can't seem to get it to actually count the alphanumeric number. I'd like to, ideally, have myself or any one in the office be able to simply type AAA100 into the first sell and have the other cells count from that (AAA101, AAA102... etc.), just as if we were only entering 100 to cell A2 and setting cell A3 as =A2+1. It needs to be that way because AAA will need to change to any of a number of other sequences (BBB, CCC, THW, whatever). Everything else seems like it's working out. I'm continuing my work to figure this out. I haven't found any where else in my search thus far that explains how I can do this. Thanks for your help though!
The problem with using the formula =A1+1 to make your increment is that Excel is attempting to add ‘1’ to the contents of the cell, which is a string. If the way you want to work is to enter the string label once, in cell A1 - then one suggest is to split the contents out into a two part formula. A fixed prefix (AAA) as part one; for the next part increment the three figure suffix (100).

Here’s a formula you can adapt to your purposes

[in Cell A1] AAA100

[Cell A2 and dragdown ] =LEFT(A1,3)&RIGHT(A1,3)+1
freekrob's Avatar
freekrob freekrob is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
28-Jan-2009, 03:58 PM #12
Quote:
=LEFT(A1,3)&RIGHT(A1,3)+1


That formula pretty much works perfectly for what I need. There are two hitches I've come across however, both of which I don't think I've mentioned.

A) the problem of leading zeros
When I'm using the formula AAA001, it comes up as AAA2, AAA3 etc in the follow cells. I've tried a could of things to remedy this but haven't found a fixed solution yet.

B) the change in the amount of numbers in the alphanumeric label
That is, what happens after AAA999? I've been trying to look into a way to have excel sense the change automatically but it seems everything I come up with has a problem or an error.

Thanks for the help. Things are starting to look a little more correct now.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
28-Jan-2009, 04:16 PM #13
Excel assumes that leading zeros are nonsense, unless the data is marked as text. So if you format your column as text and then type in numbers starting with 0 it will retain them.
Personally, I would use two columns - in A I would enter XXX and fill as needed. In B I would format the column as text, then enter 001 in B1, 002 in B2 and fill down as needed. When you get to 999, the next number will be 1000. If you want all the items to have the same number of characters, start with 0001, 0002 etc. (or add more zeros if you think that is possibly needed)
Finally, in C1 I would enter
=(A1&B1)
This will give you the results in C of
XXX001, XXX002....
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
28-Jan-2009, 04:37 PM #14
After having done the above, copy column C, then over the same data do a Paste Special, Values. This will convert the formulas to the combined data (e.g. XXX001.) Then delete the data in columns A and B. Save your file.
I would then suggest you get ASAP Utilities from http://www.asap-utilities.com/ (it is a free and very useful add-in for Excel.)
After you have installed it, you will find that there is a handy tool under #6 Columns and Rows, option #6 called Insert in-between empty rows or columns.
Highlight the data you have in column A now (XXX001 through XXX1500 or whatever it is.)
Click on ASAP Utilities in Excel, then go to the aforementioned insertion option. Select Rows.
ASAP will put a blank row between every row in your selection. Delete the one between your header and XXX001.
Now, I am not sure if you can do a Replace, but, if you can, replace all the blanks in your list with [Label 2]: AAA
turbodante's Avatar
turbodante turbodante is offline
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
29-Jan-2009, 06:51 AM #15
Quote:
Originally Posted by freekrob View Post

That formula pretty much works perfectly for what I need. There are two hitches I've come across however, both of which I don't think I've mentioned.

A) the problem of leading zeros
When I'm using the formula AAA001, it comes up as AAA2, AAA3 etc in the follow cells. I've tried a could of things to remedy this but haven't found a fixed solution yet.
You can solve the problem of leading zeros with this alteration to the formula

Code:
=LEFT(A1,3)&IF(LEN(RIGHT(A1,3)+1)=1,"00"&RIGHT(A1,3)+1,(IF(LEN(RIGHT(A1,3)+1)=2,"0"&RIGHT(A1,3)+1,(IF(LEN(RIGHT(A1,3)+1)=3,RIGHT(A1,3)+1,">EE")))))
Quote:
B) the change in the amount of numbers in the alphanumeric label
That is, what happens after AAA999? I've been trying to look into a way to have excel sense the change automatically but it seems everything I come up with has a problem or an error.
What would you like to happen after AAA999, in the formula above I have left it to return the error ">EE". But I'm sure we can cobble up something to look more professional.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑