How to use a google sheets pivot query to output strings

Question

I have a (much larger) table like this sample:

I am trying to output a table that looks like this:

The closest I can get with a pivot query returns numerical results in the value fields, rather than the desired text strings

=query(Data, "Select D,count(D) group by D Pivot B")

I resorted to a series of formulas to build my row and column headers, and then fill in the data field - See Version 3 in the sample sheet. But I couldn't figure out how to fill in the data with a single formula - as opposed to copying and pasting in the data field, which is not desirable with a dynamic number of row and column headers based on the original data.

Is there a way to wrap my data field formula (in cell B44 of the sample) in an arrayformula that will fill the data field, with a dynamic number of columns and rows?

Or even more advanced is there a formula that will deliver my desired results table in a single formula?

MattKing · Accepted Answer · 2021-07-01 14:31:43Z

2

This should work, it's a bit difficult to explain, but i could demonstrate the various parts if you opened up your sheet to editable...

=ARRAYFORMULA(TRANSPOSE(QUERY(TRIM(SPLIT(TRANSPOSE(QUERY(QUERY({CHAR(10)&A2:A11,B2:B11&"|"&D2:D11&"|"},"select MAX(Col1) group by Col1 pivot Col2"),,9^9)),"|",0,0)),"select Col1,MAX(Col3) where Col1<>'' group by Col1 pivot Col2 order by Col1 desc label Col1'Project'")))

answered Jul 1, 2021 at 14:31

MattKing

7,80310 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

MattKing Over a year ago

the department sort on mine however is happening because Producing, Directing and Acting - 1,2,3 happens to be reverse alpha. I'd need to modify the formula to account for your "sort" column.

DHolcomb Over a year ago

That is a remarkably brief formula. I suspect I could reverse engineer the parts. But if you wouldn't mind demonstrating it would probably be helpful for future folx who find this question.

DHolcomb Over a year ago

Also, I notice that the project sort isn't quite explicitly what I am looking for, but I suspect that is not a terribly difficult change.

DHolcomb Over a year ago

I forgot to say...THANKS.

MattKing Over a year ago

@DHolcomb I put a new version on your opened-up sample sheet on the tab called Reorg_mpk. It will work for up to 9999 unique projects. More than that and it would need a small edit.

|

Collectives™ on Stack Overflow

How to use a google sheets pivot query to output strings

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related