## Create number sequences in excel 2007

### Create number sequences (Autofill)

*Example 1*

- Type "1" in cell B3 + Enter
- Right click on black dot and drag down as far as needed. (See arrow)
- Click "Fill series"

*Example 2*

- Type "1" in cell A3 and "2" in cell A4
- Select A3 and A4
- Left click on black dot and drag down as far as needed. (See arrow)

### Create a repeating number sequence

In this example I am going to create a repeating number sequence 1, 2, 3, 4.

Formula in A9:

copied down as far as needed.

### Create a number sequence and restart when a cell value equals a criterion

In this example the number sequence restarts every time the adjacent cell value equals "D".

Formula in B17:

copied down as far as needed.

### Create a number sequence to count records by year and month (sorted list)

Formula in B27:

copied down as far as needed.

Alternative formula:

copied down as far as needed.

### Create a number sequence to count records by year and month (unsorted list)

Array formula in B32:

copied down as far as needed.

Alternative formula in C32:

copied down as far as needed.

### Create a number sequence to count records by year (sorted list)

Formula in B39:

copied down as far as needed.

### Create a number sequence to count records by year (unsorted list)

Formula in B46:

copied down as far as needed.

### Create a number sequence to count individual products

Formula in B52:

copied down as far as needed.

### Create a number sequence to count records within specific price ranges

Price ranges (0-500, 501-1000, 1001-1500)

Formula in B58:

+ Ctrl + Shift + Enter copied down as far as needed.

### Create a number sequence to count records by individual products and years

Formula in C64:

copied down as far as needed.

### Category: Sequence

Comments(14) Filed in category: Excel, Sequence

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 […]Comments(6) Filed in category: Excel, Sequence

Find the longest/smallest 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 […]Comments(5) Filed in category: Excel, Sequence

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 […]Comments(3) Filed in category: Excel, Sequence

Comments(2) Filed in category: Excel, Sequence

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]Comments(1) Filed in category: Excel, Sequence

### 9 Responses to “Create number sequences in excel 2007”

### 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

I have tried using the "Create a number sequence to count individual products" formula but when I copy it down it moves the second $ to before the first number. I would like to know if there is anyway of stopping this from happening

Paul,

I don´t think I understand:

when I copy it down it moves the second $ to before the first number=SUMPRODUCT(--(A52:$A$52=A52))

May be you want something like this

=COUNTIF($A$52:A52,A52)

Does (=SUMPRODUCT(--(TEXT($A$64:A64, "yyyy")&(B64:$B$64)=YEAR(A64)&B64)) + Enter) work for months

Erik Reppert,

What are you trying to calculate? Can you provide some example data?

in terms of the one of re-starting the sequence when a cell value equals a certain criterion, how about continuing the sequence by ignoring that same criterion?

so, in the case of A, B, D, D, C, B, D, A

the sequence will be if we ignore all the Ds: 1, 2, 0, 0, 3, 4, 0, 5

I'm trying to add to a hyphenated range. The sequence consists of two numbers in one cell for shipping labels, and it almost always has 40 added to each of the two numbers in it (because 40 small cartons fit in the larger shipping box).

Example: Cell B2 would read "MAB0053850-MAB0053889"

Cell B3 would read "MAB0053890-MAB0053929" and so on

The "C" column cells just have one sequence that's assigned to the larger shipping box, which contains the 40 "MAB..." cartons next to it.

Any way I can create a formula to extend these sequences vertically without splitting the two numbers into separate cells? Any help is appreciated. Thank you!

i have a serious problem..

i want to make a check voucher using excel 2007.. the problem is;i don't know how to series the check no. in the next voucher that follows.. in 1 sheet i format it into 2 voucher..

I made a template about “Insertion Sequence Numbers Automatic with Macro Codes”. You can add the sequence numbers to column that you want with your msgbox answering.

https://www.youtube.com/watch?v=hsQsW26nbNo

You can Download Here : https://netmerkez.wordpress.com/2015/08/23/insert-sequence-numbers-quickly-with-codes/