[Solved] Re-arranging cells horizontally

Discuss the spreadsheet application
Post Reply
NWSailor
Posts: 12
Joined: Tue Jan 08, 2019 8:21 pm

[Solved] Re-arranging cells horizontally

Post by NWSailor »

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
Last edited by MrProgrammer on Mon Jan 10, 2022 11:27 pm, edited 3 times in total.
Reason: tagged solved; Added formatting tags to example
OpenOffice 4.1.1 running on Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Re-arranging cells

Post by 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).
Attachments
aooS6500Survey_1.ods
(24.19 KiB) Downloaded 99 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Re-arranging cells

Post by 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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Re-arranging cells

Post by Lupp »

Sorry.
I had missed to notice that "NWSailor" was a new member to this community.
Thus I also missed to welcome him.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
NWSailor
Posts: 12
Joined: Tue Jan 08, 2019 8:21 pm

Re: Re-arranging cells

Post by 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?
OpenOffice 4.1.1 running on Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Re-arranging cells

Post by robleyd »

Have you tried Ctrl + Click to select cells.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Re-arranging cells

Post by MrProgrammer »

Hi, and welcome to the forum.
NWSailor wrote: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.
NWSailor wrote: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 96 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
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
NWSailor
Posts: 12
Joined: Tue Jan 08, 2019 8:21 pm

SOLVED: Re-arranging cells

Post by NWSailor »

woo hoo! It all works! Big thanks to everyone who helped me with this!
OpenOffice 4.1.1 running on Windows 10
Post Reply