Bug 87274 - CONDITIONAL FORMATTING: Option for reunify ranges with same conditions
Summary: CONDITIONAL FORMATTING: Option for reunify ranges with same conditions
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) Master
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
: 92769 96019 112775 (view as bug list)
Depends on:
Blocks: Conditional-Formatting-Managing
  Show dependency treegraph
Reported: 2014-12-13 01:23 UTC by m.a.riosv
Modified: 2020-01-05 22:23 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:
Regression By:

Sample file for test (8.78 KB, application/xml)
2014-12-13 01:23 UTC, m.a.riosv
Sample for test (10.17 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-12-10 16:57 UTC, m.a.riosv
File sample from scratch (10.62 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-06-15 09:48 UTC, m.a.riosv

Note You need to log in before you can comment on or make changes to this bug.
Description m.a.riosv 2014-12-13 01:23:31 UTC
Created attachment 110799 [details]
Sample file for test

E.g. copying a cell inside a conditional formatting range on other cell of the same CF range, breaks the range in the source CF and adds a new CF for the target cell.

I don't know if it must be so or not, but in variety of works we finish with a lot of CF ranges, what can affect the spreadsheet performance, specially for entering data, or for editing CF.

An option in the manage CF dialog to reunify ranges with the same CF would be very useful to reduce the complexity of work with a lot of CF, and achieve of bit of performance, while it's not automatically applied if having the ranges break have some meaning.
Comment 1 raal 2014-12-15 10:09:57 UTC
Valid enhancement, setting as NEW
Comment 2 m.a.riosv 2015-07-16 22:48:02 UTC
*** Bug 92769 has been marked as a duplicate of this bug. ***
Comment 3 thoskk 2015-08-12 22:59:48 UTC
Yes, this would be more than just useful. As a scientist, I work with lots of different conditional formattings within one sheet, frequently more than 100 cells for one condition, and several conditions all over the sheet. 
In this case,  if changes had to be made, finding and collecting the right cells is more than just annoying, it's a PitA.
Comment 4 Jonas Müller 2015-09-21 11:31:11 UTC
I think that this feature is really important.

I noticed that LibreOffice 5.0.1 easily destroys the set range for conditional formatting.


1. Create a new spreadsheet.
2. Highlight A1 to A5 and open the menu for conditional formatting.
3. Create a new condition for A1 to A5 by choosing something like "Cell is" "contains" and "done" and pick one of the templates.
4. Add a new condition and choose "Cell is" "contains" and "waiting".
5. Click on okay. As you can see you now have a clean A1 to A5 rule.
6. Now click okay and write done in A1.
8. Copy A1 to A2 and open the conditional formatting menu again.
8. As you can see now you have the ranges "A2" and in addition "A3:A5:A1".

As a result the menu will get more and more entries the more you edit the file. It can get really difficult to maintain clean conditions.

LibreOffice should not create a new condition if there is already an identical one affecting this cell.

Imo it would be great if LibreOffice would NOT change the conditions AT ALL when using copy&insert because that is usually and unwanted. So if you were to copy the word done from A1 to B2 it should not create a new condition for B2. But that's just my opinion.
Comment 5 m.a.riosv 2015-10-24 13:13:57 UTC
*** Bug 95295 has been marked as a duplicate of this bug. ***
Comment 6 m.a.riosv 2015-11-23 23:19:07 UTC
*** Bug 96019 has been marked as a duplicate of this bug. ***
Comment 7 m.a.riosv 2017-03-06 21:26:15 UTC
*** Bug 106334 has been marked as a duplicate of this bug. ***
Comment 8 7qia0tp02 2017-03-12 16:58:39 UTC
It should honor the entry "A:A" as meaning "apply this conditional formatting to the entire column" and stop auto-converting it to "A1:A1048576", which then becomes fragmented. Bug 97956
Comment 9 m.a.riosv 2017-10-01 23:45:02 UTC
*** Bug 112775 has been marked as a duplicate of this bug. ***
Comment 10 Aprax 2017-10-02 08:45:29 UTC
It appears that the main issue is that this has been called an Enhancement rather than what it really is, which is a poorly conceived and designed "Feature" and which is really a BUG that needs to be fixed. 
I can't imagine anyone wanting these characteristics as currently implemented.
It seems that calling it an enhancement justifies ignoring it for 3 years in the past and quite likely for more years in the future.
Comment 11 bonbon 2017-10-02 08:52:25 UTC
Why MS Office does not have this kind of problem? Is it better.
Comment 12 Aprax 2017-10-02 09:01:29 UTC
The best way to fix this is to remove the code that thinks that the ranges need to be split up and leave it to the user to make any appropriate and sensible changes when appropriate.
Yes, the issue can be avoided by using Copy - Paste Special with Formats unticked as suggested as a "workaround" by a LibreOffice person. But when Paste rather than Paste Special is clicked by accident (they're adjacent, it's easy to do), there's a real mess for the user to fix.

Perhaps an alternative would be to have LO-Calc ignore a click on Paste and automatically force a Paste Special without Formats when the CF has been set to apply to all possible rows as, for example, A1:A1048576. This would give the user a chance to either cancel or make changes
Comment 13 Aprax 2017-10-02 09:04:50 UTC
(In reply to bonbon from comment #11)
> Why MS Office does not have this kind of problem? Is it better.

The folks at MS actually think about the design when introducing new "Features" and test before releasing and they don't avoid having to fix their mistakes by using the description "Enhancement" and then ignoring it for years.
Comment 14 Wolfgang Jäger 2017-10-02 17:53:16 UTC
(In reply to bonbon from comment #11)
> Why MS Office does not have this kind of problem? Is it better.

Now I am talking only of the fragmentaion, not of the (re-) unification which would mostly not be needed if not first fragmentation took place.

The problem arose, as far as I can tell, at (about?) the same time as the CF manager was introduced. In advance it was not just "not visible". It was not existing at all - and it still does not exist in AOO up to V 4.3.1. (where still the classical dialog allowing for 3 conditions is used). 

To be clear about steps to get evidence for my claim: 
Create a spreadsheet document with freshly defined CF (more than one probably).
Save it.
Open it in AOO.
Copy/Paste around in a way you know would cause unnecessary fragmentation in LibO.
Save again.
Open in LibO.
Call the CF manager.
Verify: No unnecessary fragmentation.

Some developer should study the CF-"code before CF manager" and find in what way fragmentation was NEWLY introduced. Repair should be feasible then. 

Unification of "equal" CFs (how to define exactly?) can wait then.
Comment 15 Wolfgang Jäger 2017-10-02 18:09:44 UTC
See also bug#95295, which is NOT a duplicate of this bug, as also this bug is NOT a duplicate reversely. 

Two different issues, this one here only loosely related to the other one!
Comment 16 Mike Kaganski 2017-11-30 05:55:12 UTC
Commit ea55492a6e55290d92a59324b3cb31ed958981ab (https://cgit.freedesktop.org/libreoffice/core/commit/?id=ea55492a6e55290d92a59324b3cb31ed958981ab) just landed on master towards 6.1 that unifies fragmented conditional formats on loading.
Comment 17 m.a.riosv 2017-11-30 09:45:29 UTC
Really, thanks you, @Mike, I'll test when available, hope in a couple of days.
Comment 18 Mike Kaganski 2017-11-30 10:11:39 UTC
(In reply to m.a.riosv from comment #17)

That wasn't me! :) That's Tor.
Comment 19 m.a.riosv 2017-11-30 10:31:54 UTC
Forgive @Mike and specially @Tor, so big thanks @Tor.
Comment 20 m.a.riosv 2017-12-10 16:57:45 UTC
Created attachment 138346 [details]
Sample for test

Sorry, I can't see it working with attached document, maybe it's not on
Version: (x64)
Build ID: ce652a7f0d2745143a3e1078607a72695ce124f9
CPU threads: 4; OS: Windows 10.0; UI render: default; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2017-12-10_01:58:57
Locale: es-ES (es_ES); Calc: CL
Comment 21 Mike Kaganski 2018-06-15 02:41:43 UTC
(In reply to m.a.riosv from comment #20)
> Sample for test
> Sorry, I can't see it working with attached document, maybe it's not on

Well - for some reason, it doesn't work with your sample, really. Sigh; Tor's commit looked hoghly relevant to this issue.

OTOH, a fix for bug 95295 does work on that file: if you copy the range B5:D5, and then paste it back, it will re-join with the outer range, making one single conditional format with solid range.
Comment 22 Xavier Van Wijmeersch 2018-06-15 08:09:36 UTC
using attachment from comment20 its seems to work
i did a Ctrl+x and then Ctrl+z the conditional formatting corrected itself to a solid range

Build ID: c1d89a237ed678c6749213c8d517729e8a7f46a1
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group threaded

Build ID: 8447d31e529985ef7fc71933f0e55685530f9fc9
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group threaded
Comment 23 Mike Kaganski 2018-06-15 08:38:35 UTC
(In reply to Xavier Van Wijmeersch from comment #22)
> using attachment from comment20 its seems to work

We need to put a clear distinction between this issue (which is about a way to re-join formats that were fragmented before, like the Tpr's commit mentioned in comment 16, which did that for some specific case on opening an affected file), and bug 95295, which is about behaving correctly (not fragmenting formats) when working (e.g. copying) with formatted cells. So - yes, the fix to bug 95295 does work.
Comment 24 m.a.riosv 2018-06-15 09:48:20 UTC
Created attachment 142765 [details]
File sample from scratch

Sorry but I'm not  able to have it working.
Attached a new sample done from scratch.

Version: (x64)
Build ID: 8c76dfe1284e211954c30f219b3a38dcdd82f8a0
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: es-ES (es_ES); Calc: CL
Comment 25 Mike Kaganski 2018-06-15 11:29:32 UTC
(In reply to m.a.riosv from comment #24)

Please could you clarify what specifically doesn't work? As we already know, the patch that joins ranges on load doesn't work here; and the patch that works on copy-paste does.

I understand that it is off-topic in this bug, but please try this:

1. Open your attachment 142765 [details] in 6.0 or earlier; select A1; copy and paste back. See that a new formatting (third) is added - the old behaviour.
2. Open your attachment in 6.1+; repeat the above and see that the formats are still 2 (i.e., the pasted formatting was properly merged with an existing).
3. In 6.1, select A1:A6 (i.e., the whole range of one of formats), copy and paste back; see that now there's only one format (so, this joined the formats; something that might be used as a workaround for this issue).
Comment 26 m.a.riosv 2018-06-15 11:49:35 UTC
(In reply to Mike Kaganski from comment #25)
Respect to this bug, doesn't work for me, there are two equal conditions for ranges A1:A5 and A6:a16, that remain the same after save and reload.

3.- Yes only one format doing as commented,
but copying A1:A6 to A15, remains with to conditions but with ranges: 
A1:A6;A15:A20 condition 1
A6:A14;A20 condition 2

At least we have get a first good step not creating more conditions, but ranges in every condition seems a bit mess.
Comment 27 m.a.riosv 2020-01-05 22:23:13 UTC
*** Bug 129814 has been marked as a duplicate of this bug. ***