Combining rows where some cells have null values

Hi everyone. I am brand new to this forum. I searched through the threads, and have not found this issue addressed, so I'm not sure if it's even possible to do what I want to do.

I have combined three different excel spreadsheets into one, from three different reports run from my institution's databases.

Here are the column headings:

Last Name; First Name; Preferred Email; Status

Each employee has two rows, one that includes last name, first name, and preferred email; and another one which includes last name, first name, and status.

I would like one row for each employee with all four columns populated. Attached is a spreadsheet with samples. The top part is what the raw data looks like, and the bottom is what I need it to look like.

Is this even possible? I would like to avoid visual basic if possible.

Thank you!

Denise