Merging columns with Transpose, Select and Formula

[arundaka02]

Hi everyone

I want to get the following data:

Email address(unique identifier)	Core skill set	BA - Requirements Management	BA - Scenario Analysis	Hybrid - Requirements Management	Hybrid - Scenario Analysis
name1@xyz.com	BA	3	3
name2@xyz.com	Hybrid			2	1
name3@xyz.com	BA	1	3
name4@xyz.com	Hybrid			0	2

To look like this: 

Email address	BA Skill Name	BA Skill Score
name1@xyz.com	Requirements Management	3
name1@xyz.com	Scenario Analysis	3
name2@xyz.com	Requirements Management	2
name2@xyz.com	Scenario Analysis	1
name3@xyz.com	Requirements Management	1
name3@xyz.com	Scenario Analysis	3
name4@xyz.com	Requirements Management	0
name4@xyz.com	Scenario Analysis	2

I've tried using the following tools in sequence:

• Transpose
• Select - rename column headings
• Formula - TRIM to remove "BA - " and "Hybrid - " but the function is removing the first letter of the value?? (image attached)

Can anyone help?

Many thanks

KA

[Emil_Kos]

Hi @arundaka02,

Hope it helps.

[grazitti_sapna · Accepted solution]

Hi @arundaka02 , try this.

Thanks.

[arundaka02]

Thanks, @grazitti_sapna 

Made me realise that the formula tool is case sensitive and this is the reason my values were not changing!