Formula to number sequentially

[Arts]

Hi all

I have a list of items that needs numbering (goes down quite a way) but I can't seem to find a formula to number each item as there is a gap after each line.

example below (which I did manually for the first few) there is always number against text then a blank row then number... I tried a few ways using sequence and counta but cant seem to get it to number sequentially. I don't mind putting the formula in helper column and then pasting over if that makes it easier

Thanks as always

on excel 365

1	Text	Text
2	Text	Text
3	Text	Text
4	Text	Text
5	Text	Text
6	Text	Text

 

[hagia_sofia]

Hello, maybe something like:

=DROP(IFNA(TOCOL(EXPAND(SEQUENCE(ROUND(ROWS(A1:A11)/2,0)),,2)),""),-1)

 

[Cubist]

Few options:

Book2
	A	B	C	D
1	Option1	Option2	Header1	Header2
2	1	1	Text	Text
3
4	2	2	Text	Text
5
6	3	3	Text	Text
7
8	4	4	Text	Text
9
10	5	5	Text	Text
11
12	6	6	Text	Text
Sheet1

Cell Formulas
Range	Formula
A2:A12	A2	=IF(C2:C12="","",SEQUENCE(ROWS(C2:C12),,,1/2))
B2:B12	B2	=IF(C2="","",COUNTA($C$2:C2))
Dynamic array formulas.

 

[Fluff]

You can shorten that slightly like

=DROP(TOCOL(EXPAND(SEQUENCE(ROUND(ROWS(A1:A11)/2,0)),,2,"")),-1)

 

[HighAndWilder]

Hi all

I have a list of items that needs numbering (goes down quite a way) but I can't seem to find a formula to number each item as there is a gap after each line.

example below (which I did manually for the first few) there is always number against text then a blank row then number... I tried a few ways using sequence and counta but cant seem to get it to number sequentially. I don't mind putting the formula in helper column and then pasting over if that makes it easier

Thanks as always

on excel 365

1	Text	Text
2	Text	Text
3	Text	Text
4	Text	Text
5	Text	Text
6	Text	Text

I have assumed that you data starts in cell A2 but this can be altered to suit.

Conditional Formatting.xlsx
	A	B	C
2	1	Text	Text
3
4	2	Text	Text
5
6	3	Text	Text
7
8	4	Text	Text
9
10	5	Text	Text
11
12	6	Text	Text
Sheet2

Cell Formulas
Range	Formula
A2:A12	A2	=IF(ROW()=2,1,IF(MOD(ROW(),2)=0,MAX($A$1:$A1)+1,""))

 

[Arts]

Thank you all for these, sorry for the delay was on a work call. I'll mark @hagia_sofia as the solution as that was first if that's ok by all

I will go through all for learning purposes @Cubist I was close to this I had the sequence function in front as various channels suggested using sequence with count but this kept giving me a spill error...

Thanks once again!

 

[Arts]

Apologies quick follow up @Cubist and others feel free to input

Why does this formula =IF(C2="","",COUNTA($C$2:C2)) not work if the sequence function was added to it as the usual way to number is Sequence(Counta(B:B) so in my head

=IF(C2="","",(SEQUENCE(COUNTA($C$2:C2))

 

[hagia_sofia]

Apologies quick follow up @Cubist and others feel free to input

Why does this formula =IF(C2="","",COUNTA($C$2:C2)) not work if the sequence function was added to it as the usual way to number is Sequence(Counta(B:B) so in my head

=IF(C2="","",(SEQUENCE(COUNTA($C$2:C2))

Hello, it is because if TRUE the formula spills a sequence of numbers. You would need to do something like:

=IF(C2="","",MAX(SEQUENCE(COUNTA($C$2:C2))))

but it is something that is not needed as @Cubist's is perfect as it is.

 

[Arts]

Hello, it is because if TRUE the formula spills a sequence of numbers. You would need to do something like:

=IF(C2="","",MAX(SEQUENCE(COUNTA($C$2:C2))))

but it is something that is not needed as @Cubist's is perfect as it is.

Thanks for replying so another follow up (sorry) whilst on the train home hoping to learn in transit why would the MAX function make a difference here.

 

[Cubist]

Why does this formula =IF(C2="","",COUNTA($C$2:C2)) not work if the sequence function was added to it as the usual way to number is Sequence(Counta(B:B) so in my head

=IF(C2="","",(SEQUENCE(COUNTA($C$2:C2))

You're repeatedly generating sequences of 1; 1,2; 1,2,3;...ect... as you drag down so they'll overlap and cause the SPILL error.

Thanks for replying so another follow up (sorry) whilst on the train home hoping to learn in transit why would the MAX function make a difference here.

Adding MAX will eliminate the repeated values as you drag the formulas down i.e. MAX(1) = 1; MAX(1,2) = 2; MAX(1,2,3) =3; ect...