Generating a 4 part Alphanumeric Sequence

[Label-makers]

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

[npamcpp]

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?

[Label-makers]

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.

[npamcpp]

I am looking to copy down to a single cell.

Sorry, I don't know what you mean by that.

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?

[Label-makers]

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)

[abousetta]

--- Post deleted ---

Sorry misread your post. You don't want a random sample.

Can you accept a vba solution?

abousetta

[Label-makers]

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.

[abousetta]

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.

[Label-makers]

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

[npamcpp]

See attached file. Enter the word "Print" in the next empty cell in column F and the label will be generated in column G.

[abousetta]

This will create your full range in under 10 seconds.

Have a look at the attachment.

abousetta

[npamcpp]

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

[Label-makers]

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

[abousetta]

Drag the word print down to row 119,809 and leave to generate the sequences. After row 119809 you will start to get errors.

[Label-makers]

Hi,

Thank you very much for your help!

It works fantastic.

Kind regards,

Kieran

[shg]

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")

[twiggywales]

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")

can you explain a little how this works please.

[shg]

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.

[abousetta]

Here is an explanation to my code:

Please Login or Register  to view this content.

[twiggywales]

thanks for the additional info.

[Label-makers]

Hi guys,

Thank you very much for your solutions.

They all work fantastic.

Really appreciate the help.

Kind regards

Kieran