## Sequencing and numbering of batches

**Question:** I have two volumes which keeps on varying in L1 & L2 head. This is the only info i get everyday.

Now i just want under each head i.e. L1 & L2 the respective volume whether it's 4000 or any other figure say 4311 etc gets automatically divided into batches of 250 each as shown in Column A and col. D for L1 & L2 heads respectively.

If the bal volume left which is not equal to 250 then it shld appear in last row under respective head e.g. 120 in col A.

Other requirement is those batches get numbered automatically but the numbering should remain continuous when it goes from L1 to L2 head. it should not commence from 1 for L2 head. The numbering shld be done for 250 volume batches and in the last it should number for the batches not equal to 250 volume.

**Answer:**

### Array formula in A3:

copied down as far as needed.

### Array formula in B3:

copied down as far as needed.

### Array formula in D3:

copied down as far as needed.

### Array formula in E3:

copied down as far as needed.

### Download excel example file

Sequencing and numbering of batches.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

**ROWS(**array**)** returns the number of rows in a reference or an array

**INT(**number**)**

Rounds a number down to nearest integer

**Roundup(**number**)**

Rounds a number up, away from zero.

This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

David asks: Hi, I would like to use this example with my dataset, however, I'd like to visually show the […]

Removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to […]

Table of Contents Repeat values Repeat the range according to criteria in loop BatTodor asks: I failed to find right […]

Excel has a great built-in versatile tool for creating number sequences. In some situations, however, you need to rely on […]

Find the longest/smallest consecutive sequence of a value [VBA]

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

### 6 Responses to “Sequencing and numbering of batches”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Hey Oscar,

I am working on this example, hope you can help out.

Column A is the batch information I want to add, I have the data of Column B & C. How do I formulate so if the above CELL has matching critiera the batch info increase by 1. Hope you can help.

BATCHES CODE ITEM

A B C

1 0001 TV

2 0001 TV

3 0001 TV

4 0001 TV

1 0002 Sofa

1 0003 Chair

2 0003 Chair

3 0003 Chair

Val,

Cell A1: 1

Cell A2: =IF(C2=C1, A1+1, 1)

Copy cell A2 and paste cells below.

Hi Oscar,

Can you help me with my problem?

I have 2 columns named customer (A1) and OR No. (B1).

Under customer are names enumerated below them. opposite the name of customers are OR No. issued to various customers.

OR No. is in broken sequence.

My question is, how will I insert the rows for the corresponding missing OR numbers?

Example:

(A1) (B1)

Customer OR No.

customer 1 1

customer 2 2

customer 3 5

customer 4 7

customer 5 8

customer 6 10

customer 7 11

customer 8 13

customer 9 14

customer 10 15

Thanks in advance for your reply.

HughMark,

Read this post: Insert rows for missing values in excel

Hi, I would need some help from you. I would like to know how to auto sequential number rows 1,2,3. Example, when I type in col A, Cell A1 jack, col B, Cell B1 would appear the sequence number 1. Till col B reach the count/ sequence number of 15. It will repeat again from 1.

Really appreicate it if you could help me with me.

Please contact me at nowell_gemini@hotmail.com

Nowell,

Formula in cell B1:

=IF(A1<>"",1,"")

Formula in cell B2:

=IF(A2<>"",IF(B1=15,1,B1+1),"")

Copy cell B2 and paste down as far as needed.