Google Sheets – Analyzing CSV Data with Pivot Tables

csvgoogle sheetsgoogle-sheets-query

I have an issue with comma-separated values analysis and pivot tables in Google Spreadsheets. What if I have the following dataset:

Facebook, Github      Developer
No answer             Developer
Github, MSDN          Developer
Facebook, Github      Developer
Artstation            Designer
Beahnca, Artstation   Designer
3DTotal               Designer

As a result I want to see the following grouping: 

           Facebook Github  No answer   MSDN    3DTotal Artstation  Behance
Developer   2        3          1        1       0          0       0
Designer    0        0          0        0       1          2       1

How can I do that? Please assist.
The main problem is how to transform

Facebook, Github      Developer
No answer             Developer
Github, MSDN          Developer

to

Facebook              Developer
No answer             Developer
Github                Developer
Github                Developer
Github                Developer
MSDN                  Developer

and after that I can create a pivot table without any problems.

Best Answer

Assuming you are doing this once, rather than dealing with automatic processing of CSV files:

  1. Reorder the columns by dragging, so that the comma-separated one is on the right (say, it's now column B)
  2. Select the comma-separated column and choose "Data > Split text to columns" from the menu. That column will be split into several (two in your example: B and C)
  3. Use the formula ={filter({B1:B, A1:A}, len(B1:B)); filter({C1:C, A1:A}, len(C1:C))} (possibly with more columns, if you have more of them after splitting). It produces a site-category array based on each of the columns obtained in step 2, and joins them. The filtering removes blank cells that are going to appear when some comma-separated strings have fewer elements than others.

The output is the two-column table of the form that you seek.

Related Topic