Convert competency matrix in analyzable data

Tupelo1984

New Member
Joined
Jan 26, 2021
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Dear forum,

I am searching for a way to re-structure data from a skills matrix in an analyzable data model. I have the original result and know the desired outcome, but struggling to apply this to a large matrix.
Example of the data I need to convert here below in it's actual format:
TeamEmployeeBackgroundCountryHardware AHardware BHardware CHardware DHardware EHardware FSoftware ASoftware B
Xemployee 1ArtGermanyEXPERTEXPERTEXPERTEXPERTEXPERTEXPERTEXPERTEXPERT
Yemployee 2ScienceFranceADVANCEDADVANCEDEXPERTEXPERTEXPERTEXPERTADVANCEDADVANCED
Zemployee 3EngineerBelgiumADVANCEDADVANCEDEXPERTEXPERTEXPERTEXPERTADVANCEDADVANCED

The desired format needed to be able to analyze this easier and add additional grouped categories to competencies:

TeamEmployeeBackgroundCountrySkillLevel
Xemployee 1ArtGermanyHardware AEXPERT
Xemployee 1ArtGermanyHardware BEXPERT
Xemployee 1ArtGermanyHardware CEXPERT
Xemployee 1ArtGermanyHardware DEXPERT
Xemployee 1ArtGermanyHardware EEXPERT
Xemployee 1ArtGermanyHardware FEXPERT
Xemployee 1ArtGermanySoftware AEXPERT
Xemployee 1ArtGermanySoftware BEXPERT
Yemployee 2ScienceFranceHardware AADVANCED
Yemployee 2ScienceFranceHardware BADVANCED
Yemployee 2ScienceFranceHardware CEXPERT
Yemployee 2ScienceFranceHardware DEXPERT
Yemployee 2ScienceFranceHardware EEXPERT
Yemployee 2ScienceFranceHardware FEXPERT
Yemployee 2ScienceFranceSoftware AADVANCED
Yemployee 2ScienceFranceSoftware BADVANCED
Zemployee 3EngineerBelgiumHardware AADVANCED
Zemployee 3EngineerBelgiumHardware BADVANCED
Zemployee 3EngineerBelgiumHardware CEXPERT
Zemployee 3EngineerBelgiumHardware DEXPERT
Zemployee 3EngineerBelgiumHardware EEXPERT
Zemployee 3EngineerBelgiumHardware FEXPERT
Zemployee 3EngineerBelgiumSoftware AADVANCED
Zemployee 3EngineerBelgiumSoftware BADVANCED

The original data has 286 skills and 96 employees so a manual conversion is almost un-doable.
Thanks a lot!

Tupelo1984
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
How about
VBA Code:
Sub Tupelo()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long, nc As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * (UBound(Ary, 2) - 4), 1 To 6)
   For r = 2 To UBound(Ary)
      For c = 5 To UBound(Ary, 2)
         If Ary(r, c) = "" Then Exit For
         nr = nr + 1
         For nc = 1 To 4
            Nary(nr, nc) = Ary(r, nc)
         Next nc
         Nary(nr, 5) = Ary(1, c)
         Nary(nr, 6) = Ary(r, c)
      Next c
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, 6).Value = Nary
End Sub
Change sheet names to suit.
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Convert data from 'skills' matrix into analyzable data model by employee's skills in rows
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Convert data from 'skills' matrix into analyzable data model by employee's skills in rows
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
sorry wasn't aware will take in account
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top