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.
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!!
For a table like your 'start table', with any number of rows (but just the five columns shown), you can use the following:
=let(
hdr;C1:E1;
tbl;filter(A2:E;A2:A<>"");
{choosecols(tbl;1;2)\byrow(choosecols(tbl;3;4;5);lambda(row;torow({row;hdr};;1)))})
We are using torow to interlace your data and headers into a single row, calling it on a row-by-row basis within byrow.