Merging Columns and Rows of Data into a Matrix

Hi,

I've been trying unsuccessfully to find a way to merge data into a matrix, but can't quite figure out a way to manage it. I have a list of employees in column A, in the list there are employees, many with multiple instances, and in columns C onwards there are dates in dd/mm/yyyy format.

Each line represents the number of nights in a trip, from 1 to 35 nights and the start to finish dates. Where as some employees may only have one row with one date others may have 20 rows with between 2 to 35 dates.

I'd like to merge this into a matrix with one line for each employee showing which nights in the year they were away (tab matrix) which has a day of the year to illustrate which employees were away on which nights of the year.

I've uploaded an example to highlight what I mean.

I've tried a pivot and an index match formula but couldn't get either to work.

Any help would be much appreciated.

Thanks

David

Thanks Jacc,

Your help is much appreciated. I'll try and put your solution into production with my dataset tomorrow. In the interim I'll leave the post unsolved, but I think this will do just nicely.

Thanks

David

Hi Jacc,

I tried the solution over my data set and it worked perfectly.

Many Thanks for helping me out.

David