I have multiple rows of data that repeats in all columns except the last one. I want to convert the last columnn to individual rows. I figure I need to use the cross tab tool but I can't get it to work right by way of illistration my data looks like this: ManagerStateEmployeeAAAAOhioXXXXAAAAOhioYYYYAAAAOhioZZZZBBBBIndianaQQQQBBBBIndianaRRRRBBBBIndianaSSSSBBBBIndianaTTTTCCCCIllinoisUUUUCCCCIllinoisVVVVCCCCIllinoisWWWW and I want to make it look like this: ManagerStateEmployeeEmployee 2Employee 3Employee 4Employee 5Employee 6AAAAOhioXXXXYYYYZZZZ BBBBIndianaQQQQRRRRSSSSTTTT CCCCIllinoisUUUUVVVVWWWW I don't mind blanks in some of the "Employee n" columns but I need the workflow to function so that it will to accomodate enough columns to capture however many of the highest number of Employess any one manager has. Thanks!

convert rows to columns

I have multiple rows of data that repeats in all columns except the last one. I want to convert the last columnn to individual rows. I figure I need to use the cross tab tool but I can't get it to work right

by way of illistration my data looks like this:

Manager	State	Employee
AAAA	Ohio	XXXX
AAAA	Ohio	YYYY
AAAA	Ohio	ZZZZ
BBBB	Indiana	QQQQ
BBBB	Indiana	RRRR
BBBB	Indiana	SSSS
BBBB	Indiana	TTTT
CCCC	Illinois	UUUU
CCCC	Illinois	VVVV
CCCC	Illinois	WWWW

and I want to make it look like this:

Manager	State	Employee	Employee 2	Employee 3	Employee 4	Employee 5	Employee 6
AAAA	Ohio	XXXX	YYYY	ZZZZ
BBBB	Indiana	QQQQ	RRRR	SSSS	TTTT
CCCC	Illinois	UUUU	VVVV	WWWW

I don't mind blanks in some of the "Employee n" columns but I need the workflow to function so that it will to accomodate enough columns to capture however many of the highest number of Employess any one manager has.

Thanks!

Transformation

Accepted answers

nagakavyasri

Rows to cols.yxmd

All comments

WeiLi

See image below.

The tools you need is Record ID and Cross Tab. The left side panel has the settings used. The bottom section is the output.

From there, you can pick out the stuff you need / not ned.

Raj

FInd the workflow for the required output

New Workflow509.yxmd

binu_acs

@Rob48 one way of doing this

CrossTab_050124.yxmd

Raj

@WeiLi Good approach but to make this on the dynamic part on the simpler way you can just add a multirow formula and formula tool to make new headers dynamically
this will be sizable for huge volume of data as well.

Altought solution can be achieved with your logics as well.

grazitti_sapna

Hi @Rob48
I have made Workflow for your Problem. Please Check.
If it works Kindly accept it as Solution.
Thanks

Desired Output.yxzp

nagakavyasri

Rows to cols.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703