0

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

John
123-456-7890
[email protected]
Sally
987-654-3210
[email protected]
.
.
.

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

John  |  123-456-7890  |  [email protected]
Sally |  987-654-3210  |  [email protected]
.
.
.

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

Improve this question
1
  • 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
    Commented Jun 20, 2019 at 19:10

1 Answer 1

Reset to default
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.
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .