Separating column values into rows grouped by ID

[jwlam]

Hi all,

I'm trying to figure out how to get the FOS 1 and Credit values into a single row grouped by User ID. I've been trying Transpose + Crosstab but can't seem to get it.

Thanks!

Data

User ID	FOS 1	Credits for FOS 1
01140	Accounting	2
01140	Taxes	4
05518	Accounting	2
05518	Taxes	4
05518	Specialized Knowledge	4

Desired Result

User ID	FOS 1	Credits for FOS 1	FOS 2	Credits for FOS 2	FOS 3	Credits for FOS 3
01140	Accounting	2	Taxes	4
05518	Accounting	2	Taxes	4	Specialized Knowledge	4

[BU21CSEN0100791]

i also tried but no result.

[JosephSerpis · Accepted solution]

Hi @jwlam you will need to clean up your column names in order to achieve the desired results when using the Transpose and Crosstab tool. I have mocked up an approach which showcases one way to do this.

[jwlam]

Thanks @JosephSerpis! The result was almost perfect. Formula 9 gave different values for the FOS Name column when I used the actual dataset. However, I just manually moved them around to the correct columns in Excel so in the end it worked out.

[BU21CSEN0100791]

thanks great work