Combining rows where some cells have null values

[speechcommgal]

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

[oeldere]

with index / match

see the green cells in the attached file.

[etaf]

as this is a one off exercise

can you sort - so that all the rows with data in row 4 are together
then you can use an array match lookup

in your example
you can use this formula
and enter as an array using contol + shift +enter to get {} around the formula
=INDEX(D7:D11,MATCH(A2&B2,A7:A11&B7:B11,0))

[speechcommgal]

Thanks etaf and oeldere. I tried both formulas, but received value errors for both.

It might help to explain what I need. I have two Excel spreadsheets that I merged (via copy and paste) into one. One report contains lastname, firstname, and preferredemail; the other contains lastname, firstname, and status. What I need is a spreadsheet that contains all four elements, as I wish to create to email contact lists, one for PT (part-time employees) and one for FT (full-time employees).

Thanks again for any assistance that can be provided.

Denise

[etaf]

do you want to attach a sample of both sheets -and show the errors

where the names do not match or have spaces may be the issue