I try to automate as much as possible the creation of a new column and populate it with a unique "header value". I would like to automate it as much as possible and not use any additional columns to process data.
Here is the template file showing what I try to achieve
Start table
Paid Forcecasted Forcecasted
Person A Item 1 10€ 23€
Person A Item 2 22€ 7€
Person A Item 3 30€ 10€ 15€
Person B Item 4 5€ 7€ 30€
Person B Item 5 10€ 40€ 6€
Person B Item 6 10€ 5€ 8€
Person C Item 7 2€ 9€
Person C Item 8 4€
Expected result
Person A Item 1 10 Paid 23 Forcecasted Forcecasted
Person A Item 2 Paid 22 Forcecasted 7 Forcecasted
Person A Item 3 30 Paid 10 Forcecasted 15 Forcecasted
Person B Item 4 5 Paid 7 Forcecasted 30 Forcecasted
Person B Item 5 10 Paid 40 Forcecasted 6 Forcecasted
Person B Item 6 10 Paid 5 Forcecasted 8 Forcecasted
Person C Item 7 2 Paid Forcecasted 9 Forcecasted
Person C Item 8 4 Paid Forcecasted Forcecasted
I managed to succeed with the following formula =HSTACK(A2:B9;arrayformula(split(FLATTEN(C2:C9&"/"&C1);"/";;0));arrayformula(split(FLATTEN(D2:D9&"/"&D1);"/";;0));arrayformula(split(FLATTEN(E2:E9&"/"&E1);"/";;0)))
It does the job but I am sure there is a more simple way of doing it as I repeat 3 times the sequence. I initially wanted to use Query, but I do not manage to create a new column and populate it with computed data.
I could also do something very basic, which is to create 3 columns and repeat the header value inside, then query the whole thing
I am certain there a smarter, quicker ways of doing.
Many thanks!!
I don't know if this is a more simple method, but here's something you could try with LET, CHOOSECOLS, BYROW/LAMBDA, and INDEX.
=LET(header;C1:E1;BYROW(A2:E9;LAMBDA(k;hstack(CHOOSECOLS(k;1;2;3);index(header;1;1);index(k;;4);index(header;1;2);index(k;;5);index(header;1;3)))))