Convert data from 'skills' matrix into analyzable data model by employee's skills in rows

**Tupelo1984** · 03-29-2021, 10:51 AM

Dear forum,

I need to convert a large skills/competency data model where skills per employee are added per skill in a column. It makes my data difficult to analyze (plus I want to add a column with a parent category).
I have a desired outcome of what the data ideally should look like, but I am struggling in finding a more automated way of doing this rather than manual copy pasting.
The desired outcome would show my employee duplicated with their level of skill (advanced,basic,NA) under each other in a column called 'skill'.

The original data and desired outcome are attached to this thread.

My real data set is large, showing 280 skills and their level for 100 employees.

Thanks a lot.

**Norie** · 03-29-2021, 11:07 AM

You could easily do this using Power Query.

1 Select the data on 'Original Data'.

2 Go to Data>Get & Transform Data>From Table/Range.

3 In the Power Query editor select the 'Team', 'Employee', 'Background' and 'Country' columns.

4 Go to Transform>Unpivot Columns>Unpivot Other Columns.

5 Go to File>Load & Close to return the data to Excel.

**Glenn Kennedy** · 03-29-2021, 11:13 AM

A2:
=INDEX('Original data'!A2:D4,1+INT((SEQUENCE(8*COUNTA('Original data'!$A:$A)-1,,1,1)-1)/8),{1,2,3,4})

E2:
=INDEX('Original data'!$E$1:$L$1,,1+MOD((SEQUENCE(8*COUNTA('Original data'!$A:$A)-1,,1,1)-1),8))

F2:
=INDEX('Original data'!E2:L4,1+INT((SEQUENCE(8*COUNTA('Original data'!$A:$A)-1,,1,1)-1)/8),1+MOD((SEQUENCE(8*COUNTA('Original data'!$A:$A)-1,,1,1)-1),8))

all results will spill across and down.

**ben8563** · 03-29-2021, 11:33 AM

Formula:

Please Login or Register  to view this content.

**kvsrinivasamurthy** · 03-29-2021, 11:36 AM

Pl see file.
Care is taken that Columns A to D may have Duplicates in original data.
In A2 copied across up to column D

Please Login or Register  to view this content.

In E2 copied down

Please Login or Register  to view this content.

In F2 copied down

Please Login or Register  to view this content.

**Tupelo1984** · 03-29-2021, 12:22 PM

Hi Glenn,and others! thanks a lot - this seems to work but I am struggling to take over the formulas in my real sheet. I have replaced sensitive data , could you please help me out on applying this to my original file 'Skill M'. Thank you!

**JohnTopley** · 03-29-2021, 12:44 PM

Are skills designated as "N/A" to be omitted - assume anwser will be YES!

**Glenn Kennedy** · 03-29-2021, 12:49 PM

OK. I added a few more "bells and whistles" to count the skills and columns.... and wasted about 10 minutes of my life wondering why the first block didn't quite work... a missing set of parentheses. Now fixed. However, given the size of your dataset... CHECK it carefully. But these dynamic arrays are SO fast....

A2:

=LET(a,COUNTA('Skill Matrix'!$A$1:$D$1),b,COUNTA('Skill Matrix'!$1:$1)-a,INDEX('Skill Matrix'!A2:D96,1+INT((SEQUENCE(b*(COUNTA('Skill Matrix'!$A:$A)-1),,1,1)-1)/b),{1,2,3,4}))

E2:
=LET(a,COUNTA('Skill Matrix'!$A$1:$D$1),b,COUNTA('Skill Matrix'!$1:$1)-a,INDEX('Skill Matrix'!$E$1:$JZ$1,,1+MOD((SEQUENCE(b*(COUNTA('Skill Matrix'!$A:$A)-1),,1,1)-1),b)))

F2:
=LET(a,COUNTA('Skill Matrix'!$A$1:$D$1),b,COUNTA('Skill Matrix'!$1:$1)-a,INDEX('Skill Matrix'!E2:JZ96,1+INT((SEQUENCE(b*(COUNTA('Skill Matrix'!$A:$A)-1),,1,1)-1)/b),1+MOD((SEQUENCE(b*(COUNTA('Skill Matrix'!$A:$A)-1),,1,1)-1),b)))

**JohnTopley** · 03-29-2021, 01:16 PM

VBA solution which omits all N/A

Please Login or Register  to view this content.

**Norie** · 03-29-2021, 01:44 PM

If you want to remove all the NA, or any other value(s), when using Power Query all you need to do is filter them out.

**Glenn Kennedy** · 03-29-2021, 01:49 PM

Nowhere does it mention filtering out N/A....

**Norie** · 03-29-2021, 02:01 PM

Glenn

NA's were mentioned but not by the OP but by JohnTopley.

OP never indicated what, if anything, should be done with NA's (or empty cells for that matter) - all I was indicating is that the option is there, if needed, to filter them out.

**JohnTopley** · 03-29-2021, 02:20 PM

@Glenn,

There are 5772 rows if N/A are excluded but 27682 if included - so I hope you (and the OP) agree excluding N/A is sensible. I took that root when testing my VBA as I concluded one person having 280 skills was unlikely.

**Glenn Kennedy** · 03-29-2021, 04:25 PM

Youdont have 282 skills??? I don't believe that, JT!!

**Tupelo1984** · 03-31-2021, 10:13 AM

Hi everyone, thank you so much for all your solutions (I needed some time to digest them all

) I have been able to apply both the formula (Glenn, kvsrinivasamurthy ..) and the VBA from John works excellent as the data will constantly be refreshed! In reply to John's message: I would need to include N/A's too , not just the levels with a value. Can we have the same macro showing the N/A? That would be really great and give a final solution to my tread. Thanks everyone this was really helpful so far.

**Tupelo1984** · 03-31-2021, 10:14 AM

Hi John, thank you so much this really works out great for the activity I am doing as I need to refresh the skills matrix frequently. One question - I would need to include N/A's too , not just the levels with a value. Can we have the same macro showing the N/A? That would be really great and give a final solution to my thread. Thank you!

**Glenn Kennedy** · 03-31-2021, 10:22 AM

You're welcome (doesn't the formula refresh automatically, too???).

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

**JohnTopley** · 03-31-2021, 10:32 AM

Just delete this line from the macro

Please Login or Register  to view this content.

But why not use Power Query ?

**kvsrinivasamurthy** · 03-31-2021, 01:25 PM

Pl try this macro.
Here is the code which works fast.

Please Login or Register  to view this content.

**kvsrinivasamurthy** · 03-31-2021, 02:03 PM

If you want to include N/A also here is the code

Please Login or Register  to view this content.

Convert data from 'skills' matrix into analyzable data model by employee's skills in rows

LinkBack

Thread Tools

Rate This Thread

Display

Convert data from 'skills' matrix into analyzable data model by employee's skills in rows

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Re: Convert data from 'skills' matrix into analyzable data model by employee's skills in r

Thread Information

Users Browsing this Thread

Similar Threads

Adding up 2 skills matrix scoring systems and displaying on employee evaluation

workload allocation w/ skills matrix

Percentages for skills matrix

Analysing skills matrix

vlookup and condition? matching employee's skills and Ticket skills

Pivot Table Skills Matrix Trouble

Automating Skills Matrix

Bookmarks

Bookmarks

Posting Permissions