Finding A Row Number...

[Aitch]

I'm sure this is simple - but I can't get anything to work lol

Column A has a row of numbers covering a range - 1 to X

What formula will find the row when the values start from 1 again - so I can get the row where the range ends?

For example below:
1st range is 1-4 so I need the Row of the value 4 = 4
2nd range is 1-3 so I need the Row of the value 3 = 7
3rd range is 1-2 so I need the row of the value 2 = 9

1	=Row number 4
2
3
4	<<< 4
1	= Row number 7
2
3	<<< 7
1	= Row number 9
2	<<< 9
1

Thanks for any help!

 

[Scott T]

Does this do what you want?

Book1
	A	B
1	1	4
2	2
3	3
4	4
5	1	7
6	2
7	3
8	1	9
9	2
10	1	10
Sheet1

Cell Formulas
Range	Formula
B1:B9	B1	=IF(A1=1,MATCH(1,A2:$A$10,0)+IF(ROW()<>1,ROW()-1,0),"")
B10	B10	=IF(A10=1,MATCH(1,A$10:$A11,0)+IF(ROW()<>1,ROW()-1,0),"")

 

[Fluff]

Another option

+Fluff v2.xlsm
	A	B
1	1
2	2
3	3
4	4	4
5	1
6	2
7	3	7
8	1
9	2	9
10	1
Result

Cell Formulas
Range	Formula
B1:B10	B1	=IF(IFNA(MATCH(1,A2:A10,0),0)=1,ROW(),"")

 

[Aitch]

Thank you!

How can I amend this if I add an extra column for the date - can I MATCH the first non-blank cell in column A?

1st Jan	1	4
	2
	3
	4
2nd Jan	1	7
	2
	3
3rd Jan	1	9
	2
4th Jan	1	10

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C
1	1st Jan	1	4
2		2
3		3
4		4
5	2nd Jan	1	7
6		2
7		3
8	3rd Jan	1	9
9		2
10	4th Jan	1	10
Result

Cell Formulas
Range	Formula
C1:C9	C1	=IF(A1="","",INDEX(ROW(A1:A$10),MATCH(FALSE,ISBLANK(A2:A$10),0)))
C10	C10	=IF(A10="","",INDEX(ROW(A10:A$10),MATCH(FALSE,ISBLANK(A$10:A11),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aitch]

Thank you!

Is there any way which does not need an Array Formula?

 

[Fluff]

What's wrong with using an array formula?

 

[Fluff]

If you want a non CSE version, how about

=IF(A1="","",AGGREGATE(15,6,ROW(A1:A$10)/(A2:A$10<>""),1))

 

[Aitch]

Thanks Fluff, you da man!

 

[Fluff]

You're welcome & thanks for the feedback.