## Sequencing and numbering of batches in excel

**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.

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 […]You can click on headers to sort table for easy finding.

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

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.