Get the last value in columns

[ellyzadg]

Hi Everyone,

I would like to ask for your help to get the last value from each column per Employee Line Number.

Those font in black is the raw file.
Those in red are the excepted results per given row

Thank you in advance!

Note Action Year & Action is increasing per year (per column).

							EXPECTED OUTPUT
EMPLOYEE LIST	ActionDate2016 > Action	ActionDate2017 > Action	ActionDate2018 > Action	ActionDate2019 > Action	ActionDate2020 > Action	ActionDate2021 > Action	EMPLOYEE LAST MOVEMENT
EMP-001	11/01/2016 > GOV	11/01/2017 > GOV	04/28/2018 > GOV	12/01/2019 > 2019 WAGE INCR	12/01/2020 > 2020 WAGE INCR	12/01/2021 > 2021 WAGE INCR	12/01/2021 > 2021 WAGE INCR
EMP-002	11/01/2016 > GOV						11/01/2016 > GOV
EMP-003			04/28/2018 > GOV	12/01/2019 > 2019 WAGE INCR			12/01/2019 > 2019 WAGE INCR
EMP-004			11/22/2018 > 2018 WAGE INCR		01/01/2020 > MERIT ADJUSTMENT		01/01/2020 > MERIT ADJUSTMENT
EMP-005							NONE
EMP-006				12/01/2019 > 2019 WAGE INCR			12/01/2019 > 2019 WAGE INCR
EMP-007	11/01/2016 > GOV			12/01/2019 > 2019 WAGE INCR			12/01/2019 > 2019 WAGE INCR

 

[shift-del]

Hi

=XLOOKUP("*",B2:G2,B2:G2,"NONE",2,-1)