Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a huge Excel table that I need to transform into paragraphs for a Word report, and I can't find an efficient way to do it

The source looks like this: Original table format

And I would ultimately need something like this, i.e. through a pivot table. Note that "Item C", which doesn't have any description values, is skipped:

Target format

Now, to get there I believe I need to transform my source to this intermediate format, that has one description per line:

Intermediate table format

How do I get from the source to the intermediate format in an efficient way? Or maybe there is an easier way to produce the target format that I don't know of? Any help is welcome!

share|improve this question

3 Answers 3

up vote 1 down vote accepted

Simple, but probably fairly effective, way if the number of columns are not too many.

  1. Create a new empty sheet with two headers, Item and Description
  2. For each Description column
    1. Copy all from the original sheet to a new temporary sheet
    2. Delete all description columns but one (the sheet now only contains two columns, Item and Description X)
    3. Sort/filter the remaining description column to avoid all empty cells
    4. Copy/paste all remaining rows into the sheet created in the first step
share|improve this answer
    
Brilliant, that's what I was looking for! Thank you –  DavidD Apr 16 '12 at 8:16

This functionality is built right into Excel.

All you have to do is highlight the cells you wish to pivot, copy, and then paste it into another location using the "Paste transpose" function. To do this, click the down arrow underneath the Paste button in the ribbon, and under the Paste heading, you'll see a button that looks like a column rotating into a row. Click that for "Paste transpose" and your rows will become columns and vice versa in the pasted version

This video goes through the process along with a few other related tricks: http://www.youtube.com/watch?v=KbfeIYV359I

share|improve this answer

If you are ready to use scripting (like Spreadsheet::ParesExcel in Perl, for example), you can use this Stackoverflow example to form your customised solution.

There is also a PowerShell script example and some more tricks in other answers there.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.