CODEWITHSUNDEEP
google-sheets
Erich
Erich

Reputation: 2626

Pivot tables with actual entries

I have a form that populates a spreadsheet with data pairs in the form of Manufacturer-Category, like this:

Mfr Category
A   Servers
A   Workstations
B   Components
C   Workstations
D   Networking
E   Workstations
F   Other
G   Components

I would like to build a pivot table-like listing with Category entries as the column headings but with a list of Manufacturers under each:

Servers Workstations Components Networking Other
A       A            B          D          F
        C            G
        E

Categories may be added down the road, so I would like them to be detected automatically like in a pivot table.

Upvotes: 0

Views: 106

Answers (2)

Erich
Erich

Reputation: 2626

Since my data contains spaces, using anonymous's answer as a basis, I came up with the following solution:

  1. Use an intermediate sheet Sheet2 for referencing if spaces exist in the data. Cell A1 contains the formula
=ARRAYFORMULA(SUBSTITUTE('Sheet1'!A:B," ","_"))
  1. On a third sheet Sheet3, enter the following in cell A1:
=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(QUERY(Sheet2!$A$1:$B$1000,"select max(A) group by A pivot B",1),,5000))," ")),"_"," "))

Explaining what each formula does in sequence:

QUERY(Sheet2!$A$1:$B$1000,"select max(A) group by A pivot B",1) does a Google Visualization API lookup to produce the following result:

Components Networking Other Servers Workstations

                            A       A
B
                                    C
           D            
                                    E
                      F     
G

It's starting to look like a pivot table, but there are unnecessary gaps between elements of each column.

QUERY( ... ,,5000) is a straight up hack. It takes the previous query result and concatenates all the columns and their entries into a single row of cells. A pipe has been added below to denote a new cell in the row. Note the spaces between entries.

Components B G |Networking D |Other F |Servers A |Workstations A C E

TRANSPOSE( ... ) takes this row result and turns it into a column.

Components   B     G
Networking     D   
Other       F 
Servers  A      
Workstations  A  C  E

SPLIT( ... ," ") separates text into neat columns. Multiple space characters are ignored. Note: this and following functions will not display without being surrounded by ARRAYFORMULA().

Components   B G   
Networking   D      
Other        F      
Servers      A      
Workstations A C E

TRANSPOSE( ... ) puts everything back into columns:

Components Networking Other Servers Workstations
B          D          F     A       A
G                                   C
                                    E

Finally, we strip out the underscores and replace with spaces using SUBSTITUTE( ... ,"_"," ") and surround everything with ARRAYFORMULA() so it will display.

Upvotes: 0

TheMaster
TheMaster

Reputation: 50799

Try this:

=ARRAYFORMULA(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(QUERY(A1:B8," select max(A) group by A pivot B"),,5000))," ")))

You cannot order the titles though.

Upvotes: 1

Related Questions