Split, Transpose & Print Multiple Records

user_7747209 0
Viewed 120

Try to take a line item and split & transpose it before doing the same with another line item.

I'm trying to do the split and transpose operation like this

=transpose(split(c2,","))

However, this only works for one line item at a time.

Here is what the data looks like and what I'd like the result to look like.

enter image description here

array-formulas google-sheets-formula google-sheets
3 Answers

you can try this in the first line and it will give you both of the columns

={
ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(JOIN("º",REPT(A:A&"º",IF(ISBLANK(B:B),"", LEN(B:B)-LEN(REGEXREPLACE(B:B,",",""))+1))),"º")))) ,
ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(JOIN(",",IF(ISBLANK(B:B),"",B:B)),","))))
}

it parses some blank comma values as well as some empty values.

enter image description here

What it basically does is:

  • first column: checks the number of times the commas are in the second column, and get that number. Then joins the number of times the left column is repeated with a separator (in this case the separator º), like the following: tvºtvºtv, then it splits and transposes to show up vertically.
  • second column: checks just checks what is between the commas, and then transposes it, with the same type of string concatenation, this time the delimiter is the comma ,

Attention: it does not work if the second column has 2 followed commas, like the following syntax: something,,something

user_56702040

This will give you the data for all countries:

=transpose(split(join(",",C2:C),","))

user_112252910

There is a new, relatively simple way to do these problems in Sheets. Try searching for "using FLATTEN() to unpivot data". Myself and some other volunteers in the Google Product forums found a new function hidden in the back end of Google sheets and unpivoting data has proven to be it's most useful application. If you'd like the formula that will work for your particular dataset, condider sharing a sample sheet where the concept can be properly demonstrated.

user_110751290
Related