[Solved] Re-arranging cells

Discuss the spreadsheet application

[Solved] Re-arranging cells

Postby NWSailor » Tue Jan 08, 2019 8:34 pm

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 Hagar Delest on Fri Jan 11, 2019 10:35 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.1 running on Windows 10
NWSailor
 
Posts: 4
Joined: Tue Jan 08, 2019 8:21 pm

Re: Re-arranging cells

Postby Lupp » Tue Jan 08, 2019 9:55 pm

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 13 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2475
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Re-arranging cells

Postby RusselB » Wed Jan 09, 2019 1:11 am

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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5088
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Re-arranging cells

Postby Lupp » Wed Jan 09, 2019 2:15 am

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2475
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Re-arranging cells

Postby NWSailor » Fri Jan 11, 2019 6:28 am

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
NWSailor
 
Posts: 4
Joined: Tue Jan 08, 2019 8:21 pm

Re: Re-arranging cells

Postby robleyd » Fri Jan 11, 2019 8:52 am

Have you tried Ctrl + Click to select cells.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2731
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Re-arranging cells

Postby MrProgrammer » Fri Jan 11, 2019 4:16 pm

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 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
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3709
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

SOLVED: Re-arranging cells

Postby NWSailor » Fri Jan 11, 2019 7:38 pm

woo hoo! It all works! Big thanks to everyone who helped me with this!
OpenOffice 4.1.1 running on Windows 10
NWSailor
 
Posts: 4
Joined: Tue Jan 08, 2019 8:21 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests