[Solved] Re-arranging cells

[Hagar Delest]

Sorry to ask what is probably an easy question. I am a moderately experienced Excel user and recently moved to OpenOffice (love it!). I'm sure the solution to this question is in many places but it is a bit difficult for me to describe but here goes...

I have a spreadsheet of survey responses with the question at the top of the column and the cells below the question containing numbers where users rated their interest. At the bottom of the column I have the sum formula, e.g.:

Question 1 Question 2
2 7
3 9
5 11
10 27 (the totals)

I want to re-arrange this data into a horizontal format that shows the total and the question, so...

Question 1 10
Question 2 27

If there an efficient way to do this? That is better than copying and pasting each cell? I've tried auto-fill in and can't make it work.

Thanks in advance for any suggestions you can give to make this process easier!

-Ken

[Lupp]

To do such things with spreadsheets is a doubtable approach.

You may find some suggestions in the attached document.

By the way: If I am asked to respond to form questions I decline for decades now (except there is a legal basis forcing me).

[RusselB]

You could use the TRANSPOSE function a couple of times. Once for the row that identifies the Question number and once for the row that has the totals.
Please note that the totals must be in the same row.
This presumes that you don't want the individual responses as well as the totals... if you do, then only one usage of the TRANSPOSE function should work.

[Lupp]

Sorry.
I had missed to notice that "NWSailor" was a new member to this community.
Thus I also missed to welcome him.

[NWSailor]

Transpose function works great if the cells are adjacent. I know that I can move the summed data row up below the header row so I have adjacent cells that can be transposed. But I'm curious, is there a way to multiple select non-adjacent cells in OpenOffice Calc? In other words, to choose cells A1:A5 and also D1:D5 but not include the cells between these rows? I tried searching for the answer to this question but could not come up with an answer. I think this is possible in Excel (remembering back to when I learned it) but I haven't found a way to do it in OpenOffice. Is this possible?

[robleyd]

Have you tried Ctrl + Click to select cells.

[MrProgrammer]

Hi, and welcome to the forum.

But I'm curious, is there a way to multiple select non-adjacent cells in OpenOffice Calc?

Selecting non-adjacent cells in the user interface is done with ⌘click on a Mac or Ctrl-click on other systems. However, I presume you want to reference non-adjecent cells in a formula, not select them in the user interface. Calc provides the ~ formula operator for that, however it is not allowed in an array formula and using TRANSPOSE implies an array formula. Read about this operator in Help → Index → operators;formula functions or in User Guides (PDF) or searching for topics about it in the Calc Forum.

In other words, to choose cells A1:A5 and also D1:D5 but not include the cells between these rows?

The easiest way is to use two TRANSPOSE formulas as suggested by RusselB, but you can create the summary array you want with INDEX, as shown in the attachment. Note the definitions for Data, Rows, and Columns in Data → Names → Define.

201901110739.ods

(12.22 KiB) Downloaded 9 times

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[NWSailor]

woo hoo! It all works! Big thanks to everyone who helped me with this!