How to transpose a table????

[tseo]

Hello, I have a problem with transposing a table....

my data is like ;

ID	Contract name	Company	2017 sales	2017 profit	2018 sales	2018 profit	2019 sales	2019 profit
1	aaaaa	New York	1233	12	1234	13	4321	14
2	bbbbb	Peking	4221	32	5432	23	5422	54
3	ccccc	LA	98887	6777	99456	345	76432	234
4	ddddd	Sydney	135632	234	247885	234	345742	1234

and I want this to be like;

ID	Contract name	Company	Year	Data	Value
1	aaaaa	New York	2017	Sales	1233
1	aaaaa	New York	2017	Profit	12
1	aaaaa	New York	2018	Sales	1234
1	aaaaa	New York	2018	Profit	13
...	...	...	...	...	...

and so on

any tips or ideas???

Thanks.

[CharlieS · Accepted solution]

Hi @tseo 

The Transpose is the first step, but there's a couple more to get the desired output. After the Transpose, use a Text to Columns tool to split the "2017 profit" titles into separate year and name fields. Once that is done, a Select tool can rename and rearrange the fields to your desired output.

Check out the attached workflow for an example.