Columns Convert into a Rows for Large Data

Dear Frends,

I have a large data almost 1000 rows, i want to convert some columns into rows, attaching a file for your reference.

Please help me get sorted out this data.

Alright I threw this together briefly.
This will work due to the fact there are 10 Designations.
I saw that some people don't have 10 designations so those will just show a 0, so you can delete them afterwards.
Pretty much there are 2 things I created. First is column A. In Cell A2 i need you to input the very first code that is on your real "My Data" tab.
If it is actually "1" then you are already set. In cells A3 and on you can just drag that formula all the way down the column and it is going to repeat each code 10 times for you.
Then there is the section i highlighted in Purple. Its just 10 rows of vlookups. Im sure there is a better way but this should work in the meantime. You need to select the entire purple section B2:E11 and then just paste in B12 and then in B22 and so on and so on.
Pretty much every 10 rows has the same formulas. If you have 10's of thousands of rows just do the copy and paste a couple times then select like 30 of the purple rows and you can paste the formulas more and more faster.