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

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.

  • Thank you very much for even taking the time to build an example. I played with it to solve a couple of doubts and yes, this definitely solves my problem. Thanks! – Alpha Nov 6 '15 at 13:43

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.