Can't sort range with merged cells

Discuss the spreadsheet application

Can't sort range with merged cells

Postby draker » Fri Apr 22, 2011 9:23 pm

I am new to this forum, but am looking forward connecting with the OpenOffice community...

Here's my question:
I'm using the Calc application and am attempting to sort my spreadsheet.
When I attempt to sort, a window pops up that reads, "ranges containing merge cells can only be sorted without formats."
Then, when I click "ok" it won't sort... I don't understand what that means?
Can anyone help me out? Thanks!
Rick

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).
OpenOffice 3, Mac OS 10.6.7
draker
 
Posts: 1
Joined: Fri Apr 22, 2011 9:14 pm

Re: Can't sort range with merged cells

Postby Zizi64 » Fri Apr 22, 2011 9:43 pm

"ranges containing merge cells can only be sorted without formats."
I don't understand what that means?

When you use merged and unmerged cells in a column, the "sort" function will move the content of merged cells into another place. Maybe the new position of the moved content will be in an unmerged cell.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8222
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't sort range with merged cells

Postby Zom-B » Fri Apr 13, 2012 7:38 pm

Sorry for raising an almost dead thread. I have this exact problem too, for a while now, and I desperately wish for a working solution.

I have the philosophy that it should be possible.

For me, when I have ranges with merged cells, I wish for those rows/columns that intersect the merged cell to stay together, considered as a single element for sorting. For example, if I have a sheet like this:

Code: Select all   Expand viewCollapse view
--------+----------+-------
        | Volume 1 | $2.34
        +----------+-------
Comic 1 | Volume 2 | $2.99
        +----------+-------
        | Volume 3 | $2.99
--------+----------+-------
        | Volume 1 | $2.99
Comic 2 +----------+-------
        | Volume 2 | $4.10
--------+----------+-------
        | Volume 1 |
        +----------+-------
        | Volume 2 | $3.14
Comic 3 +----------+-------
        | Volume 3 | $4.00
        +----------+-------
        | Volume 3 | $4.10
--------+----------+-------

  1. If I try to sort by column A, it should keep rows 1-3, 4-5 and 6-9 as a single element and sort these three elements, keeping the relative location of the rows within the elements the same.

    Even if, for example, cells B5 and B6 were to be merged before sorting, then all of rows 4-9 should be considered a single element.

  2. On the other hand, if I try to sort by column C, it should keep rows 1-3, 4-5 and 6-9 as a single group. There are two different behaviors that one might need:
    1. Only sort rows within each group. In this example, it should perform three separate sorts, as if I manually selected the rows of each group and initiated a separate sort.
    2. Sort the groups by the first representative element of each group, which in this case are rows 1, 4 and 7 (or by the concatenation of the cells, as if those cells were merged with the option 'move contents to the first cell').
This way, there wouldn't be a problem, right?

I need both A and B1 to keep my sheets organized (I can get A by not merging cells in Column A at all, and having lots of redundancy, but then I still don't have B1)

Is this possible (using scripts or whatever), and/or should I add this as a feature request?

 Edit: made philosophy more consisten 
Zom-B
 
Posts: 13
Joined: Sun May 24, 2009 3:29 pm

Re: Can't sort range with merged cells

Postby Villeroy » Fri Apr 13, 2012 9:18 pm

This is a machine readable list:
Code: Select all   Expand viewCollapse view
Book    Volume     Price
Comic 1    Volume 1     $2.34
Comic 1    Volume 2     $2.99
Comic 1    Volume 3     $2.99
Comic 2    Volume 1     $2.99
Comic 2    Volume 2     $4.10
Comic 3    Volume 1     $3.14
Comic 3    Volume 2     $4.00
Comic 3    Volume 3     $4.10

It can be sorted, filtered and aggregated in many ways.
Cell merging is evil.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27098
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't sort range with merged cells

Postby MrProgrammer » Fri Apr 13, 2012 11:03 pm

Villeroy wrote:Cell merging is evil.
I agree. If someone feels that they must suppress the repeated items it's better to enter the data in all of the cells but then use Format > Conditional Formatting so that you don't see data if the cell above has the same information.
Attachments
201204131555.ods
(8.14 KiB) Downloaded 298 times
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: 3811
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Can't sort range with merged cells

Postby berting » Sat Oct 19, 2013 7:14 pm

OK, I agree it's evil! Now how do I find/fix the darned thing? I've got two columns containing names and currency and I've no idea where the merged cell(s) are!
OpenOffice.org 3.4.1 OSX 10.7.5
berting
 
Posts: 1
Joined: Sat Oct 19, 2013 6:52 pm

Re: Can't sort range with merged cells

Postby Villeroy » Sun Oct 20, 2013 10:40 pm

Select the whole range which includes the merged cells and unmerge.
Download http://sourceforge.net/projects/ooomacr ... on%201.01/ and install the contained Basic library.
Call macro FillBlanks_Down to fill all blank cells within the selected range with the values from the preceeding cell.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27098
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

EASY Re: Can't sort range with merged cells

Postby jimmythet » Tue Feb 03, 2015 6:04 pm

1. Select all.
2. Paste into Sublime text editor
3. Select all in Sublime
4. Paste all into a new sheet in Office
5. Profit
Libre Office, Fedora 20 KDE
jimmythet
 
Posts: 1
Joined: Tue Feb 03, 2015 6:00 pm

Re: EASY Re: Can't sort range with merged cells

Postby Villeroy » Wed Feb 04, 2015 1:31 pm

jimmythet wrote:1. Select all.
2. Paste into Sublime text editor
3. Select all in Sublime
4. Paste all into a new sheet in Office
5. Profit

Will not work with incomplete data:
Code: Select all   Expand viewCollapse view
A 1
  2
  3
B 1
  2
  3
C 1
  2
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27098
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't sort range with merged cells

Postby Alduin's Khajiit » Thu Nov 19, 2015 4:52 am

five years after this has been posted. anybody a solution? seems unresolved. I am having it the EXACT SAME as the guy with "comic 1" "comic 2" and yet this thread seems unanswered even after all these years. anybody can help now? this is a very confusing message and Google returns only this site for a relevant result. Microsoft Office can do it so should OpenOffice
OpenOffice 4.1.1 on Windows 7, OpenOffice 2.4.3 on Windows 98
Alduin's Khajiit
 
Posts: 46
Joined: Mon May 13, 2013 3:51 pm

Re: Can't sort range with merged cells

Postby Zizi64 » Thu Nov 19, 2015 9:38 am

There are more than one solutions and/or workarounds:
Microsoft Office can do it so should OpenOffice

First, a possible solution: Buy MSO.

Second, some possible workarounds: (Please read Villeroy's posts):


Third, a possible solution: Use an unique programcode for an uniqe data structure - Make your own macro for this task.

* * *

The merged cells ar NOT associated to the all of the adjacent cells anyway.
You can reference to the merged cell as the top-left cell of the merged range. Though his ONE cell will represent all of the other cells in the merged range (visually), and all of other cells (the hidden cells) in the merged range will not handled anyway.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8222
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't sort range with merged cells

Postby Lupp » Thu Nov 19, 2015 10:31 am

Sorry. Maybe I missed somthing in this thread from the ages... Did someone mention already the following?

If you want to get sorted a range containing merged cells you have to remove the checkmark next to 'Include formats' under the 'Options' tab. If you miss to do so you get the message "Ranges containing merged cells can only be sorted without formats", and this is true for the reasons explained below. If you sort without 'Include formats' the sort will include the hidden cells which may result in vanishing values and/or in some other effects seeming strange if we do not regard the explained background. If you deprecate this, you should explain how to resolve the upcoming inconsistencies. (I do not know if Excel worries about.)

Merging cells is a kind of formatting. It does not actually make vanish any cells individually. The "merged cell" is, in fact, a merged area in which the content of the lefmost topmost cell within the "merged rectangular range" is displayed with its contents and its additional formats. The other cells of the range still exist but are simply not visible because the swollen first cell occupies their area.
You cannot enter one of the overlayed cells by a mouseclick, of course, and also not by cursor movement. For some (bad imo) reasons even entering the address of one of these cells into the name field will not be accepted. This may enforce the illusion of vanished cells. It is nonetheless wrong.
If you merged cells having content everyone answering NO when prompted "Should the contents of the hidden cells be moved into the first cell?", the hidden cells keep their individual contents which can be of type number, string and formula. The values of these cells can still be referenced, and the formulae will still be recalculated if needed. If you answered "yes" on the prompt the contents (values num/text only; formulae deleted ) will be moved and leave blank cells in the background, accessible, however, in references.

I there is a software excluding the hidden cells of a merged range when sorting, this is very bad. So it is if there should be a software actually destroying (logical) cells on merge.
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: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can't sort range with merged cells

Postby buixote » Mon Dec 07, 2015 10:24 pm

Is there some way all the discussion on a thread can be replaced? Here was the answer I found in the *last* reply...

remove the checkmark next to 'Include formats' under the 'Options' tab

Or at least *start* with the solution, and allow all the computer science stuff to follow! ;-)

Cheers.

Bill
OpenOffice 2.4 on Ubuntu 9.04
buixote
 
Posts: 1
Joined: Mon Dec 07, 2015 10:21 pm

Re: Can't sort range with merged cells

Postby RoryOF » Mon Dec 07, 2015 10:47 pm

buixote wrote:Is there some way all the discussion on a thread can be replaced? Here was the answer I found in the *last* reply...

remove the checkmark next to 'Include formats' under the 'Options' tab

Or at least *start* with the solution, and allow all the computer science stuff to follow! ;-)

Cheers.

Bill


We wouldn't do that, because a solution for one person may not be a solution for others. The thread is a historical record of the discussion and path to a solution(s). You will find that frequently in OpenOffice there is more than one way to approach most requirements.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29440
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Can't sort range with merged cells

Postby Lupp » Mon Dec 07, 2015 11:51 pm

Someone definitely determined to not understand anything should get his answers from someone else.

Simply telling "the solution" as suggested above would not allow to also understand that sorting a range containing merged cells (enabled by not including formats) may seem to create a content by a miracle while sending another content to hell.
Understanding that a "merged cell" may hide another cell also conatining something, and that a visible content may get hidden when sorting by the keys in another column (eg) may be very relevant in some contexts.

Someone having understood the complications might also understand at least one of the reasons for what "Villeroy" stated merging cells to be evil. I am backing this statement.

It is a saying that it is very easy to get results from a spreadsheet, but much less easy to get meaningful results.
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: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can't sort range with merged cells

Postby opensezame » Thu Dec 27, 2018 7:05 pm

Frustrating worthless program
time to get one that works
none of these are workable

Constantly pulling stunts like this
not worth the investment in time and energy
OPenOffice v4.1.2 on win10
opensezame
 
Posts: 15
Joined: Tue Aug 16, 2016 8:51 pm

Re: Can't sort range with merged cells

Postby RoryOF » Thu Dec 27, 2018 7:53 pm

There are other programs you might use. The instability you report is peculiar to your computer and your method of working; there is no guarantee that these will not apply also to some other program.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29440
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Can't sort range with merged cells

Postby Zizi64 » Sat Dec 29, 2018 5:53 pm

Frustrating worthless program
time to get one that works
none of these are workable

Constantly pulling stunts like this
not worth the investment in time and energy


What a program you wrote about?
I am an user (not a developer) of the Libreoffice since it was forked from the openoffice. And I was an user of the OpenOffice before.
Each of the open source office suites are very usable for me, and for my Ltd.
Unfortunately the developing of the AOO is very slowed down today. But you can try the LibreOffice.
Or you can buy commercial products, like the Collabora Office: https://www.collaboraoffice.com/solutio ... nd-office/
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8222
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't sort range with merged cells

Postby plankieee » Sun Mar 10, 2019 2:12 pm

Open New Spread Sheet save with same name perhaps with addition to name no formatting

On previous spreadsheet with Can sort range with merged cells ....., Select Edit > Select All > Copy

Move cursor and place cursor on cell A1, click.

Select > Edit > Paste Special

Save

Now Select Sort > etc
Open office 3.1 on windows 7
plankieee
 
Posts: 3
Joined: Sun Apr 01, 2012 6:27 pm

Re: Can't sort range with merged cells

Postby galdar1486 » Sat Apr 13, 2019 11:24 pm

After reading the post by Lupp, I came to understand this issue. If anyone is still having this issue, the workaround is a tad tedious, but possible.

As Lupp said, "merging cells" isn't actually creating a single cell out of multiple cells (which is why, if you know anything about cell references, you know to use a merged cell in a formula, it's cell reference is the first cell in the merged cell range) but just a formatting of multiple cells representing the same data in the topmost cell.

To fix this, you have to go through and unmerge the cells and then duplicate the data into the cells thus unmerged. Then you can sort it all and do a sort on multiple criteria. For example, if one column has a number representing a rank (or as in the case of the Comic 1, Comic 2, Comic 3 example), or a list, then once Comic 1, Comic 2, and Comic 3 are listed in each cell to what they belong to, ie Comic 1 is in all three rows for Volume 1, Volume 2, etc., then you can sort first by the rank (the header for Comic 1), and then by the header for the Volume column so that the cell stay with each other.

There is probably a way to do this using an array formula as well, but honestly, it would be too time consuming to figure out and it's just easier to do the above even though it's tedious.

Hope that helps!
galdar1486
 
Posts: 1
Joined: Sat Apr 13, 2019 11:07 pm

Re: Can't sort range with merged cells

Postby Villeroy » Sun Apr 14, 2019 12:32 pm

The solution which fills missing cell values is still availlable: https://sourceforge.net/projects/ooomac ... on%201.01/

Download file
Tools>Macros>Organize>[Organizer...], tab "Libraries"
Add library "DragQueen" from the downloaded file.

Select your cells.
The installed macro can be found in "My Macros">DragQueen>cpCells>fillBlanks_Down
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27098
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 31 guests