Formula to find location of second row from the bottom containing specific text

[fishmonger]

I am trying to find a formula to identify the second row from the bottom containing the word "Summary". So in this example should return A11. Thank you

Book1
	A	B
1	01-Sep-24	1
2	02-Sep-24	2
3	03-Sep-24	3
4	04-Sep-24	4
5	05-Sep-24	5
6	Summary Sep 1 to Ssp 5	15
7
8	06-Sep-24	6
9	07-Sep-24	7
10	08-Sep-24	8
11	Summary Sep 6 to Sep 8	21
12
13	09-Sep-24	9
14	10-Sep-24	10
15	11-Sep-24	11
16	12-Sep-24	12
17	Summary Sep 9 to Sep 12	42
Sheet1

Cell Formulas
Range	Formula
B6	B6	=SUM(B1:B5)
B11	B11	=SUM(B8:B10)
B17	B17	=SUM(B13:B16)

 

[yoseeeph]

Sub Macro2()
'
' Macro2 Macro
'

'
For Each Ra In Range("A1:A17")
If Ra.Value = "" Then

Cells(Ra.Row, Ra.Column + 2) = "A" & Ra.Row
End If

Next
End Sub

 

[Peter_SSs]

I am trying to find a formula to identify the second row from the bottom containing the word "Summary". So in this example should return A11

Is one of these formulas in column D what you mean?

24 11 11.xlsm
	A	B	C	D
1	01-Sep-24	1		2nd last Summary
2	02-Sep-24	2		Summary Sep 6 to Sep 8
3	03-Sep-24	3		A11
4	04-Sep-24	4
5	05-Sep-24	5
6	Summary Sep 1 to Ssp 5	15
7
8	06-Sep-24	6
9	07-Sep-24	7
10	08-Sep-24	8
11	Summary Sep 6 to Sep 8	21
12
13	09-Sep-24	9
14	10-Sep-24	10
15	11-Sep-24	11
16	12-Sep-24	12
17	Summary Sep 9 to Sep 12	42
18
19
20
2nd last

Cell Formulas
Range	Formula
D2	D2	=INDEX(A:A,AGGREGATE(14,6,ROW(A1:A20)/(LEFT(A1:A20,7)="Summary"),2))
D3	D3	=ADDRESS(AGGREGATE(14,6,ROW(A1:A20)/(LEFT(A1:A20,7)="Summary"),2),1,4)
B6	B6	=SUM(B1:B5)
B11	B11	=SUM(B8:B10)
B17	B17	=SUM(B13:B16)

 

[fishmonger]

Is one of these formulas in column D what you mean?

24 11 11.xlsm
	A	B	C	D
1	01-Sep-24	1		2nd last Summary
2	02-Sep-24	2		Summary Sep 6 to Sep 8
3	03-Sep-24	3		A11
4	04-Sep-24	4
5	05-Sep-24	5
6	Summary Sep 1 to Ssp 5	15
7
8	06-Sep-24	6
9	07-Sep-24	7
10	08-Sep-24	8
11	Summary Sep 6 to Sep 8	21
12
13	09-Sep-24	9
14	10-Sep-24	10
15	11-Sep-24	11
16	12-Sep-24	12
17	Summary Sep 9 to Sep 12	42
18
19
20
2nd last

Cell Formulas
Range	Formula
D2	D2	=INDEX(A:A,AGGREGATE(14,6,ROW(A1:A20)/(LEFT(A1:A20,7)="Summary"),2))
D3	D3	=ADDRESS(AGGREGATE(14,6,ROW(A1:A20)/(LEFT(A1:A20,7)="Summary"),2),1,4)
B6	B6	=SUM(B1:B5)
B11	B11	=SUM(B8:B10)
B17	B17	=SUM(B13:B16)

Thank you so much. The second and third formula in D are what I was looking for. Haven't heard of aggregate formula before. Thank you for sharing!

 

[fishmonger]

Sub Macro2()
'
' Macro2 Macro
'

'
For Each Ra In Range("A1:A17")
If Ra.Value = "" Then

Cells(Ra.Row, Ra.Column + 2) = "A" & Ra.Row
End If

Next
End Sub

Thank you for the VBA coding!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.