Table transformation

Pisti93

Hi All,

Do you have a working solution for the below problem?

My data looks like this now:

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6
Value 1	Name 1	5	9	8	0
Value 2	Name 1	3	9	3	2
Value 3	Name 1	12	13	10	0
Value 4	Name 1	18	3	2	0
Value 1	Name 2	20	13	11	10
Value 2	Name 2	16	16	11	2
Value 3	Name 2	11	5	5	10
Value 4	Name 2	15	6	5	17

The layout I would like to get:

	Name 1	Name 1	Name 1	Name 1	Delta	Delta	Delta	Delta	Name 2	Name 2	Name 2	Name 2
Column 1	Column 3	Column 4	Column 5	Column 6	Delta Column 3	Delta Column 4	Delta Column 5	Delta Column 6	Column 3	Column 4	Column 5	Column 6
Value 1	5	9	8	0	-15	-4	-3	-10	20	13	11	10
Value 2	3	9	3	2	-13	-7	-8	0	16	16	11	2
Value 3	12	13	10	0	1	8	5	-10	11	5	5	10
Value 4	18	3	2	0	3	-3	-3	-17	15	6	5	17

Thanks for the help.

Ladarthure

Hi @Pisti93,

Your problem is about transposing and making cross tab and transposing to get the data formated how you want. The trick in this case, is mostly to focus to have a dataset formated in columns, then create the calculations needed and finally transpose and cross tab again to have it presented as you want.

Here are detailled steps :

1. Transpose data by having key columns as column 1 and 2 to keep them as they are and have the rest of the data transposed
2. use a cross tab with column 2 as a new header to be able to calculate the delta (you will need to keep column 1 and Name as group by values)
3. calculate the delta
4. use a transpose to have one column with name1/name2/delta (one row per value)
5. create a new column name by combining NAME and NAME2 which will give column n and the name n or delta.
6. This done, you can do a final cross tab to have the data presented the way you want.

If explanations are unclear, tell me

crosstabing.yxmd