Getting the result of last entry in each row

[dankar]

I have a large file with fields from year 2012 to 2020, I want to get the final entry for each row in the last column "A" (last year of year),

for example:

row no. 2, last entry is 2015 (G2) so the result will be "R3A"
row no 3 is blank since there is no entry at all
row no 4 , last entry is 2016 (F4) so result will be "Q6"
row no 5 , last entry is 2016 (F5) so result will be "R4B3A"
row no 6, last entry is 2018 (D6) so result will be "R4B5C3B"
row no 7, last entry is 2019 (D7) so result will be "R4B5C4C1"
row no 8, last entry is 2020 (B8) so result will be "YB1A"

	A	B	D	D	E	F	G	H	I	J
1​	Last result of year	2020	2019	2018	2017	2016	2015	2014	2013	2012
2​	R3A						R3A	R3A	R3A	R3
3​
4​	Q6					Q6	Q6	Q6	Q6	Q6
5​	R4B3A					R4B3A	R4B3A	R4B3
6​	R4B5C3B			R4B5C3B	R4B5C3B	R4B5C3
7​	R4B5C4C1		R4B5C4C1	R4B5C4C	R4B5C4
8​	YB1A	YB1A	R4B5C5B2B

Thanks

 

[jtakw]

Hi,

This is one way.

Array formula to be confirmed with CSE, if you have newer version of Excel, you may not need CSE:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Last result of year	2020	2019	2018	2017	2016	2015	2014	2013	2012
2	R3A						R3A	R3A	R3A	R3
3
4	Q6					Q6	Q6	Q6	Q6	Q6
5	R4B3A					R4B3A	R4B3A	R4B3
6	R4B5C3B			R4B5C3B	R4B5C3B	R4B5C3
7	R4B5C4C1		R4B5C4C1	R4B5C4C	R4B5C4
8	YB1A	YB1A	R4B5C5B2B
Sheet1072

Cell Formulas
Range	Formula
A2:A8	A2	=IFNA(INDEX(B2:J2,MATCH(TRUE,B2:J2<>"",0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dankar]

Hi,

This is one way.

Array formula to be confirmed with CSE, if you have newer version of Excel, you may not need CSE:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Last result of year	2020	2019	2018	2017	2016	2015	2014	2013	2012
2	R3A						R3A	R3A	R3A	R3
3
4	Q6					Q6	Q6	Q6	Q6	Q6
5	R4B3A					R4B3A	R4B3A	R4B3
6	R4B5C3B			R4B5C3B	R4B5C3B	R4B5C3
7	R4B5C4C1		R4B5C4C1	R4B5C4C	R4B5C4
8	YB1A	YB1A	R4B5C5B2B
Sheet1072

Cell Formulas
Range	Formula
A2:A8	A2	=IFNA(INDEX(B2:J2,MATCH(TRUE,B2:J2<>"",0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you so much...worked like a charm!

 

[jtakw]

You're welcome, thanks for the feedback.

 

[jtakw]

Another way, formula is Normally entered, so No CSE needed:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Last result of year	2020	2019	2018	2017	2016	2015	2014	2013	2012
2	R3A						R3A	R3A	R3A	R3
3
4	Q6					Q6	Q6	Q6	Q6	Q6
5	R4B3A					R4B3A	R4B3A	R4B3
6	R4B5C3B			R4B5C3B	R4B5C3B	R4B5C3
7	R4B5C4C1		R4B5C4C1	R4B5C4C	R4B5C4
8	YB1A	YB1A	R4B5C5B2B
Sheet1072

Cell Formulas
Range	Formula
A2:A8	A2	=IFNA(INDEX(B2:J2,MATCH("?*",B2:J2,0)),"")

 

[dankar]

one more thing please, is there a way to add the year in a new column,
so for example, for row 2 it will be 2015
...
...
row 4 =2016
row 8=2020

	H	A	B	D	D	E	F	G	H	I	J
1	Year	Last result of year	2020​	2019​	2018​	2017​	2016​	2015​	2014​	2013​	2012​
2	2015	R3A						R3A	R3A	R3A	R3
3
4	2016	Q6					Q6	Q6	Q6	Q6	Q6
5	2016	R4B3A					R4B3A	R4B3A	R4B3
6	2018	R4B5C3B			R4B5C3B	R4B5C3B	R4B5C3
7	2019	R4B5C4C1		R4B5C4C1	R4B5C4C	R4B5C4
8	2020	YB1A	YB1A

Thanks you

 

[jtakw]

one more thing please, is there a way to add the year in a new column,
so for example, for row 2 it will be 2015
...
...
row 4 =2016
row 8=2020

Yes, formulas Normally entered:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Year	Last result of year	2020	2019	2018	2017	2016	2015	2014	2013	2012
2	2015	R3A						R3A	R3A	R3A	R3
3
4	2016	Q6					Q6	Q6	Q6	Q6	Q6
5	2016	R4B3A					R4B3A	R4B3A	R4B3
6	2018	R4B5C3B			R4B5C3B	R4B5C3B	R4B5C3
7	2019	R4B5C4C1		R4B5C4C1	R4B5C4C	R4B5C4
8	2020	YB1A	YB1A	R4B5C5B2B
Sheet1072

Cell Formulas
Range	Formula
A2:A8	A2	=IFNA(INDEX(C$1:K$1,MATCH("?*",C2:K2,0)),"")
B2:B8	B2	=IFNA(INDEX(C2:K2,MATCH("?*",C2:K2,0)),"")

 

[dankar]

Yes, formulas Normally entered:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Year	Last result of year	2020	2019	2018	2017	2016	2015	2014	2013	2012
2	2015	R3A						R3A	R3A	R3A	R3
3
4	2016	Q6					Q6	Q6	Q6	Q6	Q6
5	2016	R4B3A					R4B3A	R4B3A	R4B3
6	2018	R4B5C3B			R4B5C3B	R4B5C3B	R4B5C3
7	2019	R4B5C4C1		R4B5C4C1	R4B5C4C	R4B5C4
8	2020	YB1A	YB1A	R4B5C5B2B
Sheet1072

Cell Formulas
Range	Formula
A2:A8	A2	=IFNA(INDEX(C$1:K$1,MATCH("?*",C2:K2,0)),"")
B2:B8	B2	=IFNA(INDEX(C2:K2,MATCH("?*",C2:K2,0)),"")

Oh wow, You are the best ! thank you soooo much!

 

[jtakw]

You're very welcome.