Can't sort range with merged cells

Discuss the spreadsheet application
Locked
draker
Posts: 1
Joined: Fri Apr 22, 2011 9:14 pm

Can't sort range with merged cells

Post by draker »

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).
 Edit: This ten-year-old topic has been locked. If you want to ask a new question, open your own topic. 
OpenOffice 3, Mac OS 10.6.7
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't sort range with merged cells

Post by Zizi64 »

"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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Zom-B
Posts: 13
Joined: Sun May 24, 2009 3:29 pm

Re: Can't sort range with merged cells

Post by Zom-B »

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

--------+----------+-------
        | 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 
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't sort range with merged cells

Post by Villeroy »

This is a machine readable list:

Code: Select all

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4883
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Can't sort range with merged cells

Post by MrProgrammer »

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 511 times
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).
berting
Posts: 1
Joined: Sat Oct 19, 2013 6:52 pm

Re: Can't sort range with merged cells

Post by berting »

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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't sort range with merged cells

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jimmythet
Posts: 1
Joined: Tue Feb 03, 2015 6:00 pm

EASY Re: Can't sort range with merged cells

Post by jimmythet »

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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Alduin's Khajiit
Posts: 46
Joined: Mon May 13, 2013 3:51 pm

Re: Can't sort range with merged cells

Post by Alduin's Khajiit »

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
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't sort range with merged cells

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can't sort range with merged cells

Post by Lupp »

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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
buixote
Posts: 1
Joined: Mon Dec 07, 2015 10:21 pm

Re: Can't sort range with merged cells

Post by buixote »

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
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Can't sort range with merged cells

Post by RoryOF »

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.15 on Xubuntu 22.04.4 LTS
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can't sort range with merged cells

Post by Lupp »

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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Can't sort range with merged cells

Post by RoryOF »

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.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't sort range with merged cells

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
plankieee
Posts: 3
Joined: Sun Apr 01, 2012 6:27 pm

Re: Can't sort range with merged cells

Post by plankieee »

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
galdar1486
Posts: 1
Joined: Sat Apr 13, 2019 11:07 pm

Re: Can't sort range with merged cells

Post by galdar1486 »

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!
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't sort range with merged cells

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tacklingdata
Posts: 2
Joined: Thu Jun 24, 2021 12:54 am

Re: Can't sort range with merged cells

Post by tacklingdata »

I resolved this without a Macro (tried the Macro first, couldn't figure out where the DragQueen macro was).

My steps:
+ Select All
+ Format --> Untick Merged Cells (Disclaimer: You may lose some content)
+ Sort

Note: It looked like my merged cells had a small red triangle on the right side. Not the easiest to find, and there was no tooltip.

Hope it helps!
OpenOffice 5.1.9 on Mac OS 11.2.3
Locked