Data extract with macro on multiple repeated values

Hi,

I am finding it very difficult to extract the Project reference data for each employee, I have attached sample working file for your reference. Please help with the appropriate solution to extract this.

For Example

I have a project names in Rows and Employee names in Columns and each employee fill the working efforts in multiple projects and i would like to extract which all project they have worked for each month. Since the data is very huge i am not able to progress fast in collecting the data

Project Name Emp1 Emp2 Emp3
A
B 10 15
C
D 20 120 10

Thanks

with attachment

I tried with Copy and Transpose as well as Pivot Tables .. but this seems to be unique issue .. VBA could be the solution to this. Please help

Out put on new sheet.

Thanks AB33 ... this is great stuff, how do i make it a dynamic if i have running columns and rows for extended data?

The code could handle any rows and 2 columns. I used current region for a range, so, as long as there is no complete blank row or column, the code should work.