Home > Enterprise >  How can I rearrange rows in a sheet into columns
How can I rearrange rows in a sheet into columns

Time:01-01

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. enter image description here

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 enter image description here

Results
enter image description here

  • Related