0

If I have a list of data in a spreadsheet like this:

John
123-456-7890
john@email.com
Sally
987-654-3210
sally@email.com
.
.
.

What would be the quickest and cleanest way to sort the data into rows and columns like

John  |  123-456-7890  |  john@email.com
Sally |  987-654-3210  |  sally@email.com
.
.
.

I have found the forumula =OFFSET(Sheet1!$A$1,(ROW()-1)*#,0) that will help me get data from every # (eg 5th) row but I was wondering if there was a cleaner way

  • 1
    That general type of solution is a pretty standard way of doing this. Use a formula in the three columns that does the math to identify which row's cell goes in that position. – fixer1234 Jun 20 '19 at 19:10
2

This method will help you to Copy Data from Rows into Columns:

enter image description here

  • Enter this Formula in Cell C1 then fill Right & Down.

    =OFFSET($A$1,COLUMNS($A:A)-1+(ROWS($1:1)-1)*3,0)
    

If your data starts from other then Row 1, then you may use this Formula:

  • Formula in Cell D109, fill it Right then Down.

    =OFFSET($A$109,COLUMNS($A:A)-1+(ROWS($109:109)-1)*3,0)
    

enter image description here

N.B.

  • In used Data set every new name occurs after 3 Rows therefore (ROWS($1:1)-1)*3, mutiplied by 3, which is editable, as the Row interval changes.
  • Adjust Cell references in the Formula as needed.

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.