Excel Row

[arvindhari]

Hi with reference to the image what formula can i have in f1 and g1 to get the answer (which i hard coded into those cells). Basically f1 should have the value in E corresponding to the row of the first non blank value in column F and the value in E corresponding to the row of the Last non blank value in column F. I hope i explained it well but if u stare at the image you will see what I mean. Thank you.

 

[Fluff]

Hi & welcome to MrExcel.

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[dreid1011]

Yes, please answer Fluff about your version, as it will dictate what solutions are available. In the meantime, something like this perhaps:

Book1 7-29-2024.xlsm
	D	E	F	G
1			a4 a10	a6 a12
2	1	a1
3	2	a2
4	3	a3
5	4	a4	600
6	5	a5	78
7	6	a6	806	239
8	7	a7	234	919
9	8	a8	86	838
10	9	a9	246	694
11	10	a10	271	241
12	11	a11		454
13	12	a12		12
14	13	a13
15	14	a14
16	15	a15
17	16	a16
Sheet1

Cell Formulas
Range	Formula
F1:G1	F1	=INDEX($E$2:$E$17,SMALL(IF(F$2:F$17<>"",ROW(F$2:F$17)-1,""),1))&" "&INDEX($E$2:$E$17,LARGE(IF(F$2:F$17<>"",ROW(F$2:F$17)-1,""),1))

Though there may be more efficient options.

 

[Fluff]

Thanks for updating your profile.
As you have 365 another option

Fluff.xlsm
	D	E	F	G
1			a4 a10	a6 a12
2	1	a1
3	2	a2
4	3	a3
5	4	a4	600
6	5	a5	78
7	6	a6	806	239
8	7	a7	234	919
9	8	a8	86	838
10	9	a9	246	694
11	10	a10	271	241
12	11	a11		454
13	12	a12		12
14	13	a13
15	14	a14
16	15	a15
17	16	a16
Sheet5

Cell Formulas
Range	Formula
F1:G1	F1	=LET(f,FILTER($E$2:$E$100,F2:F100<>""),TAKE(f,1)&" "&TAKE(f,-1))

 

[arvindhari]

Thank you that solution worked. I had a more convoluted (and VERY inelegant) solution

=INDIRECT("E"&ROW(INDEX(F2:F17, MATCH(TRUE, INDEX((F2:F17<>0), 0), 0))))&" "&INDIRECT("E"&ROW(OFFSET(F2,COUNTA(F:F)+1,0)))

 

[arvindhari]

Thanks for updating your profile.
As you have 365 another option

Fluff.xlsm
	D	E	F	G
1			a4 a10	a6 a12
2	1	a1
3	2	a2
4	3	a3
5	4	a4	600
6	5	a5	78
7	6	a6	806	239
8	7	a7	234	919
9	8	a8	86	838
10	9	a9	246	694
11	10	a10	271	241
12	11	a11		454
13	12	a12		12
14	13	a13
15	14	a14
16	15	a15
17	16	a16
Sheet5

Cell Formulas
Range	Formula
F1:G1	F1	=LET(f,FILTER($E$2:$E$100,F2:F100<>""),TAKE(f,1)&" "&TAKE(f,-1))

Thanks your ( first formula didnt work on another sheet ).. this formula works beautifully

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Cubist]

Another option:

Book2
	D	E	F	G
1			a4 a10	a6 a12
2	1	a1
3	2	a2
4	3	a3
5	4	a4	600
6	5	a5	78
7	6	a6	806	239
8	7	a7	234	919
9	8	a8	86	838
10	9	a9	246	694
11	10	a10	271	241
12	11	a11		454
13	12	a12		12
14	13	a13
15	14	a14
16	15	a15
17	16	a16
Sheet1

Cell Formulas
Range	Formula
F1:G1	F1	=TEXTJOIN(" ",,XLOOKUP(TRUE,F2:F17<>"",$E$2:$E$17,,,{1,-1}))