This might be impossible to do without a ton of expensive scripting, but I would like to run it by the experts in case I'm missing something. It's hard to explain (because it's nonsensical.. i.e. not my choice), so I'll just give a very simplified example.
My source data sheet is like this...
+----------+-------+------+--------+
| Date | Time | Cars | Trucks |
+----------+-------+------+--------+
| 01/01/19 | 08:00 | 2 | 12 |
| 01/01/19 | 12:00 | 4 | 10 |
| 01/01/19 | 20:00 | 6 | 8 |
| 01/02/19 | 08:00 | 8 | 6 |
| 01/02/19 | 12:00 | 10 | 4 |
| 01/02/19 | 20:00 | 12 | 2 |
+----------+-------+------+--------+
.. and I want to have another sheet dynamically display it like ...
+----------+---------------+---------------+---------------+
| | 08:00 | 12:00 | 20:00 |
+----------+------+--------+------+--------+------+--------+
| | Cars | Trucks | Cars | Trucks | Cars | Trucks |
+----------+------+--------+------+--------+------+--------+
| 01/01/19 | 2 | 12 | 4 | 10 | 6 | 8 |
| 01/02/19 | 8 | 6 | 10 | 4 | 12 | 2 |
+----------+------+--------+------+--------+------+--------+
In other words, a column for each time at category combined. Keep in mind that, in reality, this is a large data set. Also, I have a little bit of flexibility in the headers in the sense that, the two header rows in the output could be one. Something like "Cars 8:00", "Trucks 8:00", "Cars 12:00"... etc
Does anybody know how this could be done with a pivot table? Or some other simple'ish method?
Here's a live version of the same example... https://docs.google.com/spreadsheets/d/1npQikx3Zwa2QZwDAk8IxyawYw2hkeYpPe9Nh4ImkZAE/edit?usp=sharing
try:
=ARRAYFORMULA({{TEXT(SUBSTITUTE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(
QUERY({Source!A2:B, TRANSPOSE(QUERY(TRANSPOSE(Source!C2:D),,999^99))},
"select Col1,max(Col3) where Col1 is not null group by Col1 pivot Col2"),
"limit 0", 1)),,999^99)), " "), "1899-12-30", ), "hh:mm"), ""}; {"",
SPLIT(REPT(Source!C1&" "&Source!D1&" ", COUNTUNIQUE(Source!B2:B)), " ")};
TRANSPOSE(QUERY(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY({Source!A2:B,
TRANSPOSE(QUERY(TRANSPOSE(Source!C2:D),,999^99))},
"select Col1,max(Col3) where Col1 is not null group by Col1 pivot Col2"),
"offset 1", 0))&" ",,999^99)), " ", 1, 0)), "where "&JOIN(" or ", "Col"&
ROW(INDIRECT("A1:A"&COUNTUNIQUE(Source!A2:A)))&" is not null"), 0))})