packing list serial number generator for boxes

[yogitheguru]

​i want to pack books in boxes based on the total count route wise,area wise and subject code wise
the excel sheet has the following columns
centrecode,routeno,areano,subjectcode,subjectname,noofpackets


for example
1006 1 1 n23 history 80 --> after this a new column has to be created where the total no of boxes required must appear,serial number of six digits from- to,it must be the continuity of numbers for each centrecode


can some one help me to do this using vba , i am new to vba but i need to do this to stay in my present job.
this is very urgently required

[SamT]

Which one below?


1006 1 1 n23 history 80 1 of 3 100001
1006 1 1 n23 history 80 2 of 3 100002
1006 1 1 n23 history 80 3 pf 3 100003


1006 1 1 n23 history 80 3 100001 - 100003

[yogitheguru]

the second one is the expected one

centrecode routeno areano subcode subname totalpackets serialnumber

1006 1 1 n23 history 80 100001 to 100080

the next row will be
1006 1 1 n24 english 230 100081 to 1000311
like this it goes on

[yogitheguru]

the first method also is best provided subtotal is shown for each centrecode

[yogitheguru]

the first method also is best provided subtotal is shown for each centrecode

I am attaching the sample file and have mentioned how i need the output as

kindly help me to generate serial number

[SamT]

Each individual Item in each Package shipped must have it's own serial Number? See line 14 in your latest example.


I don't think a 6 digit serial number is enough. If you shipped 1000 Items or Packages each day, you will run out of unique serial numbers in barely 30 months.

Consider a two part serial: Centre + Subject & 6 digit unique serial: 100612-nnnnnn, or 100612 nnnnnn. Even better for code purposes 1006-12-nnnnnn or 1006.12.nnnnnn or 1006,12,nnnnnn.

In most computer Operating systems, every day has a unique "Serial" number: In Windows, today is day number 44064. Prefixing Unique 6 digit Package Serial numbers with this number allows 100,000 packages each day without ever duplicating serial numbers.

All those suggestions are easily used by any programming language, especially VBA for Excel.

Please note that actually programming for a unique number is easy, What is hard is clarifying and perfecting your requirements, Programmers are of necessity very literal pedants.

[yogitheguru]

Hi thanks for the reply

it is for each 30’s pack a serial number and for each 10’s pack a serial number to be generated also this is not a long term project so my boss wants only it to be a six digit number

in my sample data the count is mentioned for 30’s and 10’s in each row that has to be split into those many serial numbers as shown the output

i dont know what is the function to split the row based on the count that is why i am seeking help

kindly assist me

[yogitheguru]

You are my only hope now for solving this

[SamT]

Dude,

You have changed the structure of the data and the requirements of the output in every post so far. We can't work with that.

[yogitheguru]

Dude,

You have changed the structure of the data and the requirements of the output in every post so far. We can't work with that.

Hi
I have not changed the structure of the data,I was trying to explain it more clearly so that I get the solution
I thought someone like you as an excel expert will help me to solve my issue.
I am trying all means to solve it but unable to come to a conclusion one more person also tried it but now he also is unable to....
anyway thank you very much for the response and I am sure now that I cannot improve my vba skills for my career