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:
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:
Now, to get there I believe I need to transform my source to this intermediate format, that has one description per line:
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!
Simple, but probably fairly effective, way if the number of columns are not too many.
- Create a new empty sheet with two headers, Item and Description
- For each Description column
- Copy all from the original sheet to a new temporary sheet
- Delete all description columns but one (the sheet now only contains two columns, Item and Description X)
- Sort/filter the remaining description column to avoid all empty cells
- Copy/paste all remaining rows into the sheet created in the first step
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:
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.