3

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?

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 | |
  • 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 Jun 14 at 19:46
  • Thanks. Just going with this answer as it is the cleanest solution so far. – Daniel May Jun 14 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 | |
  • 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 Jun 14 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 Jun 14 at 22:52

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.