Ask for Help

Hi everyone,

I have the assignment as below (project.xlsx and termination.xlsx). I need to have come up with the final result in file (final ter 1.xlsx) But I don't know how to using transpose to make all Name, department, access level, and some more rows to be header column. Please help me. Thank you very much.

Project.xlsx

Termination 1.xlsx

final ter 1.xlsx

Help

Accepted answers

Yoshiro_Fujimori

@thua ,

When I use Cross Tab Tool, I would design the table to input to Cross Tab (with the parmeters in mind).

In this case, I would group by "Field n" information, and I want a "Name" column to be used as Column name of the output table.

Once I get the image, I would transform the original table to fit the image.

Workflow

The first Cross Tab is to arrange the pair of [Name] and [Value] to feed to the Second Cross Tab.

Formula Tool

[Type] = IF Mod([Row], 2) = 1 THEN "Name" ELSE "Value" ENDIF

[Row] = CEIL([Row] / 2)

Output

I hope this helps.

Good luck.

workflow.yxzp

All comments

AncientPandaman

you need to
1. assign the column number and header id

2. transpose

3. join the header and value by Name and header id

4. crosstab.

Field 1	Field 2	type	id
Name	Name	header	1
A	B	value	1
De	De	header	2
HR	HR	value	2

type	id	Name	Value
header	1	Field 1	Name
value	1	Field 1	A
header	2	Field 2	Name
value	2	Field 2	B

header	Name	value
Name	Field 1	A
Name	Field 2	B

Name	Name	De
Field 1	A	HR
Field 2	B	HR

Yoshiro_Fujimori

@thua ,

When I use Cross Tab Tool, I would design the table to input to Cross Tab (with the parmeters in mind).

In this case, I would group by "Field n" information, and I want a "Name" column to be used as Column name of the output table.

Once I get the image, I would transform the original table to fit the image.

Workflow

The first Cross Tab is to arrange the pair of [Name] and [Value] to feed to the Second Cross Tab.

Formula Tool

[Type] = IF Mod([Row], 2) = 1 THEN "Name" ELSE "Value" ENDIF

[Row] = CEIL([Row] / 2)

Output

I hope this helps.

Good luck.

workflow.yxzp

thua

Thank you soo muchhhhh Yoshiro. I am learning and still confused on using transpose tool to get new arranged columns and data as I want but I can understand it more when I look into your workflow. Also very detail answer. Thank you very much !!!!

thua

I appreciate your help, but I get lost on it a bit. I was doing it back and forth and been while but couldn't get the new columns as I want, was so confused using transpose and cross tab tool.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703