Based on: https://stackoverflow.com/questions/52376162/combining-column-data-in-google-data-studio

I have a long list of data, about 200 rows, and each row contains multiple columns of data and a region column. In this region column, it contains a list of regions for example, New York, Italy, Mexico. I want to be able to split the list into X rows where X is the number of items in the list but each row contains the elements from the row.

From the example, 3 rows would form: a row containing New York, a row containing Italy and a row containing Mexico.

The stackoverflow link this is based on is something that's really close to what I'm looking for but it contains extra bits like counting and it doesn't seem to really work when the content contains spaces.

Example: https://docs.google.com/spreadsheets/d/1q5zpf37zFF8Y9pTAZgpmRky2v_hJXuFgpRF2A9nfY7s/edit?usp=sharing

I want to have this new data be put into a new sheet and not the existing one if possible.

  • Welcome to webapps. Please share an example sheet. Include data as well as the desired result. – marikamitsos Aug 1 '19 at 21:16
  • Thanks, I've added a sample sheet. – Andrew C Aug 1 '19 at 21:22
  • Include data as well as the desired result. That means in the question here we should see sample data and what you expect to get. This is what I have and This is what I want. If your spreadsheet link ever disappears the question must still be clear. – user55949 Aug 2 '19 at 8:53

That was a fun challenge, i think i did it using only formulas and arrays.

Here's a sample spreadsheet

So i just split the regions with SPLIT then if there's a region 1 it add a row, if there's a region 2 it add a row, etc... then regroup everything with arrays. You can improve my work with some Arrayformula for automation or Array_constrain to filter blank rows of just Filter.

First you split the regions


Then test for each region if there's one and if the answer is yes, you get a new row. Here's the rough version :


On your result tab you just fetch for every result and the header :


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