I have a list of cases.
Each row represents a status change with its own timestamp.
Each case can have types, such as water, gas, electricity, all with the same case ID.
How can I group into 1 single row, per type, all of the different status changes with their respective dates.
It doesnt matter if its excel or google sheets.
I'm attaching an example file.
You can set two formulas like this:
=QUERY(QUERY(Data!B1:K,"select B,C,D,H,K,count(C) group by B,C,D,H,K",1),"SELECT Col1,Col2,Col3,Col4,Col5 where Col1 is not null",1)
And in F2:
=SCAN(,SEQUENCE(COUNTA(A2:A)),LAMBDA(a,q,
TRANSPOSE(FLATTEN(
FILTER(Data!E2:G10,Data!B2:B10=INDEX(A2:A,q),Data!C2:C10=INDEX(B2:B,q),Data!D2:D10=INDEX(C2:C,q))))))
I changed those columns in order to be able to add as many columns as changes there are in each of the cases without trouble. Let me know if that's helpful. Here you have my