Google Sheets Guide – How to Convert Repeating Rows into Columns

formulasgoogle sheetsgoogle-sheets-arraysgoogle-sheets-query

I have data in Google Sheets that looks like

    A        B
1 Name:  John Smith
2 Email: john@smith.com
3 Name:  Dan Jones
4 Email: dan@jones.com
... many rows

which I'd like to get into this format:

   A            B
1 Name        Email
2 John Smith  john@smith.com
3 Dan Jones   dan@jones.com
...

This is different from just a straight transpose because we're dealing with repeating sets of rows that need to be repeatedly transposed into columns.

What's the best, fastest way to do this?

Best Answer

You can use the following query formula: 

={{"Name";QUERY({B1:B},"where Col1 is not null and  not Col1 contains '@'")},
  {"Email";QUERY({B1:B},"where Col1 contains '@'")}}

enter image description here

Functions used:

Related Topic