Looking up values if there are duplicates

[sandman85]

Hi,

Hoping someone might be able to help me with this.
I have a sheet which shows which consultant is working on a given project in a given week.
As the sheet is organised by project, a consultants name can appear multiple times if they finish on one and move on to another.
On a seperate sheet i am trying to show the availability of the consultants by drawing on the data in the first sheet.
Unfortunately vlookup only gives me the first value and index requires unique values.
This is what the first sheet looks like:

Projects Project Function Consultant 6-Apr 13-Apr 20-Apr 27-Apr 4-May 11-May 18-May 25-May 1-Jun 8-Jun 15-Jun 22-Jun
Project Name PM FZ y y y y
Project Name PM AS y y y y
Project Name PM BF y y y y
Project Name PM FZ y y y y

For consultant FZ is there a way i can pick up the values from two rows and combine them in one?
This will happen a lot so hoping the answer is easy.

Many thanks.

Sandy

[sandman85]

Sorry my example didn't turn out how i'd hoped. Hopefully you get the drift.

[jraj1106]

Hello..
Can you upload a sample excel file with data?

[sandman85]

Hi.

Attached is an example.
I would like to look up the data in sheet one in sheet two to combine it.

Thank you for any assistance. Much Appreciated.