Merge List of different activities for each Category of employee to a list of employees

[Dmercier]

Each PERSON needs to be scored for each CAPABILITY
I have 200 People in 12 Job Categories and a SEPARATE list of capabilities for each job category. How do I merge these so I can avoid copy paste nightmare

[#2 · Accepted solution]

@Dmercier I guess this is something that it well suited to Power Query. Are you familiar with that?

The attached file has a working solution based on your example. One structured table named "Capabilities" and one called "Employees". 

First, clean up both tables a bit, merge them and do some final cleaning and load the end result back to Excel. May be a bit confusing if you have never used it before. 

Give it a try. Use the attached file. Delete the original tables (keep the green one though) and create two new ones (anywhere within the workbook) containing all data, with the same table names as mentioned above. It's important that the real tables have exactly the same structure and column names as the ones in your example. Once done, press Refresh All on the Data ribbon and the green table should updated in a matter of seconds. No copy / paste needed. 

Come back here if you have any problems.

[#3]

@Riny_van_Eekelen 

Thank you!  I am getting familiar with Power Query in PowerBI. 

I have never used it in Excel.  I am reviewing the data transformation now.  

I understand the data model:  Bring tables in separately, Merge them - full outer-join, clean table 

And I see how you did the index.

Way simpler than copy paste!  Thank you for taking the time to provide this to me.

Dave

[#4]

@Dmercier Great! Glad I could help you figure it out.