I am trying to use a Query to replicate the behaviour pictured below. I have a pivot table with two values (as rows) displaying a job name and it's status. This works fine with the built-in pivot table.
I've then tried to recreate this using the following Query formula:
=QUERY(A1:D6, " select C, max(A), max(D) GROUP BY C PIVOT B Order by C ")
This gives me the result below. This is as close as I've managed to get to replicating the original pivot table.
Edit
The main part of this question has been solved below but I'm wondering how to extend this out if I had more columns of data that I then wanted to display in the same way. Below I have added an additional column 'Other'.
CodePudding user response:
Try:
=Index({"",Transpose(Unique(Filter(B2:B,B2:B<>"")));Flatten({Sort(Unique(C2:C)),IFError(Unique(C2:C)/0)}),IFNA(VLookup(Transpose(Unique(Filter(B2:B,B2:B<>"")))&Flatten({Text(Sort(Unique(C2:C)),"hh:mm"),Text(Sort(Unique(C2:C)),"hh:mm")&".1"}),{Flatten({B2:B&Text(C2:C,"hh:mm"),B2:B&Text(C2:C,"hh:mm")&".1"}),Flatten({A2:A,D2:D})},2,0))})