Batch Numbering

[mandarp22]

Hi everyone my question is:

I have an excel sheet containing membership number say 2827000001 to 2827005000 in column A now I want break it down into a batch of 100 and put the batch number in column B in front of each membership number automatically. i.e. Column A row 1 to 100 - 2827000001 to 2827000100- column B row 1 to 100 1, Column A row 101 to 200 - 2827000101 to 2827000200- column B row 101 to 200 2 and so on

thanks

 

[Luke M]

In C2:

=QUOTIENT(A2-1,100)+1

Copy down as needed.

 

[NARAYANK991]

Hi ,

See your file now.

Narayan

 

[mandarp22]

Hi

Luke M / Narayank991

I am very much delighted by both the solutions as was struggling for few days now. Both the solutions are working fine and solve the purpose. However in the solution by Narayank991 I find the BONUS of flexibility to decide the batch size and the beginning of the batch numbering. This made me a bit greedier and asks one more question……actually I need to generate the barcode (code128) for the membership number. At present I am generating it with the help of other software and copy paste each barcode manually one by one in the sheet. Is it possible to generate the barcode in excel itself??

Thank you once again for the help with batch numbering………..

 

[Naresh Yadav]

Hi,

you can also try

=CEILING(COUNT($B$2:B2),100)/100

 

[bosco_yip]

Some other :

=ROUNDUP(A2/F$1,0)

=INT((A2-1)/F$1)+1

Regards