Page 1 of 1

"Mirroring" the spreadsheet

PostPosted: Sat Dec 08, 2007 8:34 pm
by dandaman
I want to import a spreadsheet into a database, and I have the problem that the spreadsheet has all the field names in the vertical column, and data written horizontally. Is there a way to "mirror" the spreadsheet in Calc? If there is not, is there any way to import the spreadsheet as it is?

Help is greatly appreciated


Re: "Mirroring" the spreadsheet

PostPosted: Sun Dec 09, 2007 5:17 am
by Sliderule

You asked:
Dandaman wrote:Is there a way to "mirror" the spreadsheet in Calc?

The answer is yes, I can think of two 'quick' methods:
  1. TRANSPOSE function
  2. transposing tables
Method 1 ( TRANSPOSE function ), according to Calc Help:
Calc Help TRANSPOSE function wrote:TRANSPOSE
Transposes the rows and columns of an array.

Array represents the array in the spreadsheet that is to be transposed.
More explanations on top of this page.

In the spreadsheet, select the range in which the transposed array can appear. If the original array has n rows and m columns, your selected range must have at least m rows and n columns. Then enter the formula directly, select the original array and press Shift+Ctrl+Enter. Or, if you are using the Function Wizard, mark the Array check box. The transposed array appears in the selected target range and is protected automatically against changes.

Method 2 ( transposing tables ), according to Calc Help:
Calc Help transposing tables wrote:Rotating Tables (Transposing)
In Calc, there is a way to "rotate" a spreadsheet so that rows become columns and columns become rows.
  1. Select the cell range that you want to transpose.
  2. Choose Edit - Cut.
  3. Click the cell that is to be the top left cell in the result.
  4. Choose Edit - Paste Special.
  5. In the dialog, mark Paste all and Transpose.
If you now click OK the columns and rows are transposed.

I hope this helps, please be sure to let me / us know.


Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.