Hi,
I am trying to use excel to generate an 4 part Alphanumeric sequence.
Sequence is ascending from right hand side to left i.e order is ascending Part 4, then Part 3, then part 2, then part 1
Example
'A - 01 - 01 - 01'
Part one must be only A to H
Part 2 to be from 1 to 72
Part 3 to be 1 to 13
Part 4 to be 1 to 16
last String to be 'H - 72 - 13 - 16'
Can anyone one help
Kind regards
Kieran
Can you explain in a bit more detail what you expect to happen? Do you want a formula to copy down? Do you want users to select from valid values?
I am looking to copy down to a single cell.
I am trying to generate a location label with barcode for pallet racking in a large warehouse.
The warehouse has isles A to H, Bays 1 to 72, Levels 1 to 13 and locations 1- 16
So, I need a single non repeating string for each location in excel format link to our barcoding printer software.
Sorry, I don't know what you mean by that.I am looking to copy down to a single cell.
Do you want to generate a random but unique code and then put the stuff into that isle, bay, level and location or do you want to specify location, level , bay and isle and then generate the appropriate label? Can you describe the process? What is the input? The output is obviously the individual code, but how do you want to get there?
Hi,
Must be sequential code that we can labelled each location in order to make locating goods easier. Can't be random as we wouldn't find the goods.
1 code per cell in excel so that our software can print the required code.
The input im cell A1 to be A-01-01-01.
I then need a formula to generate a sequential code of A-01-01-02 all the way to H-72-13-16 for a total of 119,808 codes (16 x 13 x 72 x 8)
--- Post deleted ---
Sorry misread your post. You don't want a random sample.
Can you accept a vba solution?
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Hi,
Appreciate your help.
And sorry, new to this!
Our barcode software will generate a barcode from and excel file so not sure about VBA?
Was hoping an excel formula could copy down from A-01-01-01 where some rules are set i.e the last part of the string would be sequential from 1 - 16 then the second last segment would then go sequential from 1 - 13, etc...
Not sure how to explain this other than a warehouse needs a unique number to each location but the whole wre house must be in some sort of sequentila coding to be able to locate a single location.
Do you need the sequence (all 119,808 codes once) or generated on user input? If once, vba is not a problem because it will create all the sequences in second and you can then feed that to your barcode generator.
Yes, all at once.
I have attached an excel file trying to explain what I am doing but so far I have to gerneate the codes manually. Hoping there is a faster solution
See attached file. Enter the word "Print" in the next empty cell in column F and the label will be generated in column G.
This will create your full range in under 10 seconds.
Have a look at the attachment.
abousetta
That is awesome.
Only issue is it only goes down to 'E'.
What formula do I use to generate the complete list?
Kind regards
Kieran
You can put these columns anywhere in any sheet and you can also start with any label number by setting the starting value in the first data row of the table. Of course, you can hide columns A to E
Drag the word print down to row 119,809 and leave to generate the sequences. After row 119809 you will start to get errors.
Hi,
Thank you very much for your help!
It works fantastic.
Kind regards,
Kieran
Another way: In A1 and copy down,
=CHAR(INT((ROW()-1)/(16*13*72)) + 65)
& TEXT(MOD(INT((ROW()-1)/(16*13)), 72) + 1, "-00")
& TEXT(MOD(INT((ROW()-1)/16), 13) + 1, "-00")
& TEXT(MOD(ROW()-1, 16) + 1, "-00")
Entia non sunt multiplicanda sine necessitate
Originally Posted by shg
can you explain a little how this works please.
The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
<--- If a post helps hit the star
It takes the row number (minus one) and divides it into pieces. The modulus 16 (plus one) is the rightmost part; divide by 16 and the modulus 13 (plus one) is the next part; divide by 16*13 and the modulus 72 (plus one) is the next part; divide by 16*13*72 and add 65 to get the leftmost character.
Here is an explanation to my code:
Sub BarCode() ' Declare variable types (I gave them random letters - a, b, and c but could be x, y, and z or any other letter or name you choose Dim a As Integer, b As Integer, c As Integer, d As Integer, e As String, r As Long ' Create four nested loops For a = 1 To 8 For b = 1 To 72 For c = 1 To 13 For d = 1 To 16 ' go to next row r = r + 1 ' Convert the variable "a" to one of the letters you wanted e = Choose(a, "A", "B", "C", "D", "E", "F", "G", "H") ' Concantenate all the numbers and letter together Range("A" & r) = e & " - " & Format(b, "00") & " - " & Format(c, "00") & " - " & Format(d, "00") Next Next Next Next End Sub
Last edited by abousetta; 06-06-2012 at 01:01 PM.
thanks for the additional info.
Re: Generating a 4 part Alphanumeric Sequence
Hi guys,
Thank you very much for your solutions.
They all work fantastic.
Really appreciate the help.
Kind regards
Kieran
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks