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.
9 Responses to “Create number sequences in excel 2007”
Leave a Reply
How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
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/