Google Sheets - How to transform table data

Question

I have a data with and ID and numeric value, organized by date in columns, like this

ClusterID	Pos. 2024-01-01	Pos. 2024-01-02	.....	Pos. 2024-03-24
100015	1053	1027	.....	327
230022		9002	.....	101
......			.....
320024	74	98	.....	2

And I would like to have this data in rows:

ClusterID	Position	Date
100015	1053	2024-01-01
100015	1027	2024-01-02
100015	....	....
100015	327	2024-03-24
100022		2024-01-01
100022	9002	2024-01-02
100022	....	....
100022	101	2024-03-24
100024	74	2024-01-01
100024	98	2024-01-02
100024	....	....
100024	2	2024-03-24

I tried transpose() and query but stuck with big amount of columns in my sample.

Here is the link with some data in GS: https://docs.google.com/spreadsheets/d/186hcvRHUAN3VRPwMO5jaZbF8ozft-waxTGpCCh-uPFQ/edit?usp=sharing

Answer 1

Here's one approach you may test out:

=let(Σ,tocol(,1), reduce(Σ,Sheet1!B2:index(Sheet1!CG:CG,match(,0/(Sheet1!A:A<>""))),lambda(a,c,vstack(if(iserror(a&""),Σ,a),
                  if(len(c),hstack(index(Sheet1!A:A,row(c)),c,mid(index(Sheet1!1:1,column(c)),6,10)),Σ)))))

Answer 2

Here's another approach:

=arrayformula(let(
maxrow,counta(Sheet1!A2:A),
maxcol,counta(Sheet1!B1:1),
clusterID,offset(Sheet1!A2,,,maxrow),
date,regexextract(offset(Sheet1!B1,,,,maxcol),"Plc. (.+)"),
position,offset(Sheet1!B2,,,maxrow,maxcol),
split(tocol(if(len(position),clusterID&"|"&position&"|"&date,),1),"|")))

We are defining the maximum dimensions of your position matrix then using these values to define three variables corresponding to clusterID, position & date (using regex to extract the date). We then collect all the values together in a string for each non-empty position using a 2D if, flatten it to a single column using tocol and then split the separate values back out into separate columns using split.