Changing rows into columns in Excel

0

I have a table that looks like this in Excel:

|firstname |Greg     |
|secondname|Armstrong|
|firstname |Joe      |
|secondname|Bloggs   |
|firstname |Jane     |
|secondname|Doe      |

And I need to change it to look like this:

|firstname|secondname|
|Greg     |Armstrong |
|Joe      |Bloggs    |
|Jane     |Doe       |

I'm not particularly well versed in all the functionality that Excel has so I don't even know what to look for.

Any help would be appreciated. Thanks.

insidious_elk

Posted 2016-05-19T13:57:43.290

Reputation: 3

unpivot - search that. Or transpose. Either way. – Raystafarian – 2016-05-19T14:01:27.263

1

Possible duplicate of How to Split one column in Excel into multiple columns of 55 rows

– Máté Juhász – 2016-05-19T14:22:17.150

There are already several similar question here with good ideas, please have a look on them. (http://superuser.com/questions/583595/move-every-7-columns-into-new-row-in-excel?rq=1, http://superuser.com/questions/554666/how-to-split-one-column-in-excel-into-multiple-columns-of-55-rows, http://superuser.com/questions/617017/how-can-i-transpose-and-group-data-in-excel-2010?rq=1, http://superuser.com/questions/668063/pasting-from-several-rows-into-a-single-row-every-6th-cell?rq=1) If none of the solutions work, please share what have you tried and why that doesn't work.

– Máté Juhász – 2016-05-19T14:23:58.830

Answers

1

Assuming the original table is in columns A and B, pick any cell and enter:

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

copy this cell across and down:

enter image description here

Note:

Using the right formulas, you can always map any single column or row into a 2-D table and any 2-D table into a single column.

Gary's Student

Posted 2016-05-19T13:57:43.290

Reputation: 15 540

Thank you! You are a genius. I would never have found this. – insidious_elk – 2016-05-20T10:26:58.903

@insidious_elk thank you for the feedback ...........there are many similar formulas to re-organize data ............ no VBA is needed. – Gary's Student – 2016-05-20T10:39:13.273

Asked: 2016-05-19T13:57:43.290

Viewed: 116 times

Active: 2016-05-19T22:04:24.460