Conditional formatting for groups of rows defined by column contents

English
,
#1

I have some data which is in groups of irregular numbers of rows, e.g.

1 Dragon
2 Dragon
3 Dragon
4 Dog
5 Dog
6 Cat

(edited to avoid confusion)

I want to colour each group in alternate colours so it is easy to see the groups. So in the exmple above rows 1 -3 would be pale yellow, rows 4-5 would be pale blue, row 6 would be pale yellow and so on.

I think there must be a way using styles and conditional formatting but I just can’t do it. (or must I resort to macros?) Currently the dataset is small so I can do it by hand, but it will grow soon and I’d like to find a way of doing this.

Any ideas? Thank you

#2

The groups must be identified in one way or another. How is it done in your case?

#3

Just a comment to the title of your question: If you …apply the same format as the row above… you’ll end up with an uniformly (with respect to rows) formatted sheet (proof by mathematical induction), which is in contradiction to the details.

#4

Well, it is the frst step to solve the problem I thought I could set row 1 to a pale yelow style and then do something like this: a2=a1 then apply same format as row 1 i.e. pale yellow style; otherwise apply alternate pale blue style. In the example above that would mean that row 4 would not match row 3 and the alternate pale blue style would be applied, and Row 5 matches row 4 so would be the same format; it changes again at row 6 to return to pale yellow style.

#5

The groups are identified as in the example above (I have change the items as I realised that I had chosen to use coloyrs which may confuse the issue since I am trying to apply a style with a colour) - they are text
so if we can make it work for the example above then it will work for for my larger, more complex sheet

#6

Your description doesn’t build groups. The groups must follow some rule related to your data. Let’s make an simple example: Assume column A contains dates - one rule could be:

If day of date = Monday assign to Red Group and color all cells of rows with same day “red” background
If day of date = Tuesday assign to Blue Group and color all cells of rows with same day “blue” background
If day of date = Wednesday assign to Green Group and color all cells of rows with same day “green” background
… and so on.

The point is: This rule (identification) needs to based on data in your sheet. Without relating your colors to values / data you neither can create conditional formatting nor I can see any way to create a macro.

And that’s what @Lupp requested:

What are the categories to be build the groups for your specific problem. If you don’t specify the only answer could be: Use conditional formatting, but the “How to” is what you want to know.

#7

Cont’d From your last comment, I take that you have words which should build the groups. What are these words? How many words (=groups), and which colors should be assigned to each word. And Are these words known in advance or are you looking for some kind of dynamic solution, which evaluates all different Words by its own (building its own categorization this way) and assigns some color?

#8

I am struggling to see the problem, although I think I confused the issue by choosing data that used colour names and I am also speaking of colouring the rows.
One column hold a text value for the groups, there are many groups so if I test that I will know if the row is in the same group as the previous row. I want alternating (only two) colour styles that change with each group change so would look something like this: 2020-02-10_1557 the grouping column in this example is ‘table_name’
Is it clearer now?

#9

No struggling needed insofar:
-1- You can attach an example file showing your intentions in detail.
-2- You surely understand that a conditional format needs to be based on a clearly defined condition.
The second issue is about how the groups you were talking of are defined in your mind.
May I assume they are meant to be contiguous rows containing the same text in column A, but being separated from the previous and from the subsequent “group” by a change in that keyword text?
(The subject of your question actually is poorly worded, isn’t it? I wouldn’t expect someone looking for a solution of the same or very similar problem to find his (f/m) way to this topic.)

Based on this assumption I made an example for you. It is attached to my answer below.

#10

I’m a bit tired and wouldn’talk about the idea. Thus I simply attach this example and trust in your uderstanding.

#11

That works perfectly, thank you. Perhaps when you are so tired you can explain in English what the sumprodcut formula is actually doing.

#12

The subjevt was worded like this because I thought I could find a solution and copy the format from the previous row. However the solution doesn’t do this so it is badly named. I will change it

#13

You actually won’t get an answer concerning a conditionally overlaid format even if you write user code to ask a respective cell for it. The cell simply doesn’t know itself about it. It’s only the cell grid of the current view for which CF gets evaluated, and it must be this way to be efficient enough, imo. You can ask a cell for its applied CellStyle and for lots of formatting attributes. But then again you cannot use such a style within a CF. The cell style associated there with a condition always is a constant. No formula accepted. You may file a feature request to bugs.libreoffice.org.
There once was circulated a way to use variable styles with CF resorting to the STYLE() function by misusing the expression in the ‘Formula is’ mode. I would dissuade from using it, and it may even no longer work at all.

#14

Quoting @Lizat: “The subject was worded like this because …”
I have to apologize. In fact already the original subject expressed clearly what you hoped to get by CF and I read it superficially. My poor English may have contributed to misunderstandings. However, the answer on the original question taken literally would have been a simple “no way”.
What you finally got by my answer was inferior to your objective, of course. This mainly because it doesn’t allow to define the CellStyle to overaly to the formatting of additional rows of any group by assigning it to the first row. The logical reason is the basic difference between overlaying and assigning a CellStyle. Technical reasons queue up.

#15

Quoting @Lizat: “Perhaps when you are so tired you can explain in English what the sumprodcut formula is actually doing.”
Sorry. I didn’t understand the joke. But I may try one myself: It makes the formula work as expected.
The comparison placed on the single parameter position of SUMPRODUCT() needs “iterative” array-evaluation in the first place. The arrays cannot be compared as arrays after all, but constitute a sequence of inequations. SUMPRODUCT’s parameters are specified ForceArray. This means that every single comparison is performed and the result put into an array of simple results. As with any array given to SUMPRODUCT() it will then add the elements. Since a TRUE is equivalent to 1 in Calc it (roughly) gets the number of group-borders this way. Alternating groups are distinguished by this number changing from odd to even …
BTW: The name SUMPRODUCT is, of course, misleading in a ridiculous way. Is it MS heritage? In fact the name is used for a sum of products

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.