Ask for Help

[thua]

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.

[PangHC]

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 · Accepted solution]

@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.

[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.