1

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.

Sample Data: Sample data

Resulting pivot table: 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.

Query

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'.

New data: New data

New desired result: enter image description here

herman
  • 123
  • 10
  • Is there any issue with the pivot table? I think that's a better approach rather than using a query, due to the calculated columns with combined data, I wouldn't do it, but maybe your use case requires it – David Salomon Feb 08 '22 at 01:37
  • sadly i need a little more flexibility than pivot tables seem to offer. – herman Feb 08 '22 at 21:13

1 Answers1

1

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))})

enter image description here

z''
  • 4,527
  • 2
  • 3
  • 12
  • 1
    Interesting! Can confirm that this does indeed work. Will have to take some time to digest what is going on here now. Thanks – herman Feb 08 '22 at 21:14
  • Curious to know if there is a way to do this with more columns of data, like in my edit to the original post? – herman Feb 10 '22 at 20:51
  • 1
    @herman yes: https://stackoverflow.com/a/71080343/5632629 – player0 Feb 11 '22 at 12:51