Filter rows based on multiple conditions, keeping all columns

Hi there,

I'm looking to create a table showing the history of currently active employees. Employees are in rows and years are in columns. The source data is one tab for each year, unioned together (I cannot change this structure.)

My table currently shows the status (Active = Y or New or null) for every employee for every year. I want to filter to show only those employees/rows who are Y or New in 2022, and show all years/columns for those employees. I was able to filter to only currently active employees, but then it only showed the 2022 column.

Packaged workbook is attached. In my example, I would want to filter out Employees 3 and 4, and show all columns for Employee 2.

Any advice is appreciated.

Thank you!