3

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.

3

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.

  • It works! Great! The splitting returnes me B:G columns. In that case my formula will look like ={filter({B2:B, A2:A}, len(B2:B)); filter({C2:C, A2:A}, len(C2:C)); filter({D2:D, A2:A}, len(D2:D)); filter({E2:E, A2:A}, len(E2:E)); filter({F2:F, A2:A}, len(F2:F)); filter({G2:G, A2:A}, len(G2:G))} Am I right? Is here any way to do it more readable? Thanks for your help. – Slava Nov 29 '16 at 8:54
  • Looks right and no, I don't think there is a shorter way to express that, considering one can't put a for loop into a spreadsheet formula. – user135384 Nov 29 '16 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.