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 IDFOS 1Credits for FOS 1 01140Accounting201140Taxes405518 Accounting205518Taxes405518Specialized Knowledge4 Desired Result User IDFOS 1Credits for FOS 1 FOS 2Credits for FOS 2 FOS 3Credits for FOS 3 01140Accounting2Taxes 4 05518Accounting2Taxes 4Specialized Knowledge4

Separating column values into rows grouped by ID

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

Parse

Accepted answers

JosephSerpis

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.

Pivot_18102023.yxmd

All comments

BU21CSEN0100791

i also tried but no result.

JosephSerpis

Pivot_18102023.yxmd

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.

Field Rank Formula.jpg

Dynamic Rename.jpg

BU21CSEN0100791

thanks great work

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335