#StackBounty: #google-sheets #google-sheets-query #google-sheets-arrayformula Create a pivot table with a column for each assignee, con…

Bounty: 100

I have a two-column table with the first column being “task description” and second column being “assignee”. This is something I’m unable to influence.

Crude representation:

enter image description here

I’d like to be able to create a pivot table in a different sheet with a unique column for each assignee, containing rows of task descriptions assigned to that person.

What I’d also like to do is having each cell in this table to actually be a =HYPERLINK(...) to the corresponding task description cell in the original sheet. This is something, as I understand, that is impossible with built-in pivot tables. I’ve tried constructing a pivot table with those specifications and succeeded, but the links were not clickable.

enter image description here

“HYPERLINK” column formula is

=HYPERLINK("#gid=0&range=A" & MATCH(Task, Tasks!A1:A, 0), Task)


Is it possible to accomplish this?

I’ve fiddled around with =QUERY(...) with mixed success as I’ve been unable to automatically generate columns for the table. What I have so far is query that generates headers for the table, and a query that generates rows for that header, but I have to manually copy and paste the second query – it’s not automatically applied to ALL columns.

This is an example of what I’d like to do. This is the closest I’ve gotten to the solution but still not quite right.


UPDATE

I’ve managed to avoid using =QUERY completely, and switched to =ARRAYFORMULA with =FILTER and {;} syntax.

enter image description here

All that is left with this approach, is to skip empty cells in columns. This is something I have no idea how to handle.

If anyone could chime in, I’d be really grateful.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.