Split multiple excel columns into rows

Question

I have a spreadsheet with data like this:

98929     11.38      3.39      9.10
98930     15.24      2.86      7.05

Each value is in a separate column. I need it to display like this:

98929     11.38
          3.39
          9.10
98930     15.24
          2.86
          7.05

Is this possible?

http://stackoverflow.com/a/10922351/293078 – Doug Glancy Jun 06 '14 at 21:28 — Doug Glancy, Jun 06 '14 at 21:28

chrisb · Accepted Answer · 2014-06-06T21:56:27.260

There is a TRANSPOSE array function that can be used transpose data, but for most scenarios I find it easier to use copy->paste-special->transpose.

http://www.techrepublic.com/blog/microsoft-office/transpose-excel-data-from-rows-to-columns-or-vice-versa/

Edit: Based on the data structure you want, using a pivot table might be a relatively easy way to reshape the data. Assuming the following data structure

Key       a       b       c
98929   11.38   3.39    9.1
98930   12.38   4.39    10.1
98931   13.38   5.39    11.1

Add a pivot table based on your data and

Add 'Key' to the rows section
Add 'a', 'b', 'c' (etc) to the values section
Move the 'Values' entry (i.e., summation sign) to the rows sections
Turn off subtotals, grand totals, and use the 'Tabular' layout

Here's a picture, if the text above isn't clear.

pivot table layout

It will depend a little on your version of Excel, but in the Ribbon, click on the 'Design' tab with the pivot table selected, and chose 'Do not show' under both the subtotals and grand totals options in the upper left hand corner, and select the 'Tabular' option under Report layout.

pivot table settings

Thank you, I did try that, but I have several hundred rows and it will take forever to do it one by one. It doesn't seem to let me do more then one at a time. Plus I need to maintain the data in the first column. — user3606142, Jun 06 '14 at 21:16

score 0 · Answer 2 · edited May 23 '17 at 12:27

Here is one solution, assuming your "Key" values are greater than 9000, and the other column values are not (more generally speaking, assuming there is no intersection between "Key" and other columns).

Join all columns in just one unique line using the text editor NotePad++. See here, how to do it.

Then, transpose this unique line to one unique column. For example, transpose on column B, starting on cell B3. Then, type on cell A3:

=IF(B3>9000,"Delete This Row",IF(OFFSET(A3,-1,1)>9000,OFFSET(A3,-1,1),""))

Drag the formula down to all rows in column A where there are data on column B.

It should yield a result similar to this:

enter image description here

Alright, now filter column A by "Delete This Row" values and delete all filtered rows.

Split multiple excel columns into rows

2 Answers2

Linked