3

I have data in Google Sheets that looks like

    A        B
1 Name:  John Smith
2 Email: [email protected]
3 Name:  Dan Jones
4 Email: [email protected]
... many rows

which I'd like to get into this format:

   A            B
1 Name        Email
2 John Smith  [email protected]
3 Dan Jones   [email protected]
...

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?

Improve this question

2 Answers 2

Reset to default
3

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:

Improve this answer
2
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well.
    – marikamitsos
    Commented Jun 14, 2020 at 19:46
  • Thanks. Just going with this answer as it is the cleanest solution so far.
    – Daniel May
    Commented Jun 14, 2020 at 21:51
1

In C1 enter:

=index(B:B,2*row()-1)

and copy downward. In D1 enter:

=index(B:B,2*row())

and copy downward:

enter image description here

If you need to enter the formulas below row#1, replace:

row()

with:

rows($1:1)
Improve this answer
2
  • Thanks for this. I'm just going for the voted answer because it's just a bit cleaner with not having to copy downward the formula.
    – Daniel May
    Commented Jun 14, 2020 at 21:51
  • @DanielMay I agree with you completely! The marikamitsos post is really cool. I am going to up-vote it myself.
    – Gary's Student
    Commented Jun 14, 2020 at 22:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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