Google Sheets – Create Multiple Columns from a Single Column

google sheetsworksheet-function

I've got information that I gather from emails, from where I just take the lines, pass them through a filter and drop them into a Google Spreadsheet sheet.

It ends up looking like this:

Element 1: Value 1.1
Element 2: Value 1.2
Element 3: Value 1.3
Element 1: Value 2.1
Element 2: Value 2.2
Element 3: Value 2.3
...

If I know that these are always going to be 3 values, and then it repeats over and over, is it possible for me to transpose that list only to 3 columns?

My goal is to end up with:

Element 1 | Element 2 | Element 3
----------+-----------+----------
Value 1.1 | Value 1.2 | Value 1.3
Value 2.1 | Value 2.2 | Value 2.3

Best Answer

First, I'd split the data from column A into two columns. In this case it looks like the length is consistent so it makes it easier (I can demo the other if need be).

Get the left chunk with =left(A2,11)

Get the right chunk with =right(A2,10)

Now you have Element 1: | Value 1.1

To flip the unique elements use =transpose(unique(B2:B7))

Now you have Element 1: | Element 2: | Element 3:

Finally, you can filter the returned data using =FILTER( C:C , B:B = E1 )

The working example is here.

Related Topic