How do I add a row when the data changes in LibreOffice Calc
-
- Posts: 4
- Joined: Thu Apr 16, 2020 4:15 pm
How do I add a row when the data changes in LibreOffice Calc
Hi,
I am trying to add a row when the cell data changes. I am having 1500 plus rows on my sheet. For example, my data is like this:
December 3, 2018
December 12, 2018
December 18, 2018
December 29, 2018
January 1, 2019
January 15, 2019
January 20, 2019
May 7, 2019
May 17, 2019
May 23, 2019
May 25, 2019
July 23, 2019
July 24, 2019
July 26, 2019
What I want to achieve is:
December 3, 2018
December 12, 2018
December 18, 2018
December 29, 2018
January 1, 2019
January 15, 2019
January 20, 2019
May 7, 2019
May 17, 2019
May 23, 2019
May 25, 2019
July 23, 2019
July 24, 2019
July 26, 2019
Can anyone help?
Thank you in advance.
I am trying to add a row when the cell data changes. I am having 1500 plus rows on my sheet. For example, my data is like this:
December 3, 2018
December 12, 2018
December 18, 2018
December 29, 2018
January 1, 2019
January 15, 2019
January 20, 2019
May 7, 2019
May 17, 2019
May 23, 2019
May 25, 2019
July 23, 2019
July 24, 2019
July 26, 2019
What I want to achieve is:
December 3, 2018
December 12, 2018
December 18, 2018
December 29, 2018
January 1, 2019
January 15, 2019
January 20, 2019
May 7, 2019
May 17, 2019
May 23, 2019
May 25, 2019
July 23, 2019
July 24, 2019
July 26, 2019
Can anyone help?
Thank you in advance.
Last edited by Pankaj2006 on Fri Apr 17, 2020 10:59 am, edited 2 times in total.
Pankaj Trivedi
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
Re: How do I add a row when the data changes in LibreOffice
You must WRITE a macro to achieve this task.I am trying to add a row when the cell data changes.
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.
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.
Re: How do I add a row when the data changes in LibreOffice
Whatever you try to do -- BULLSHIT. Don't do that!
If you want to aggregate your data by month, your software provides several powerful tools that do not depend on fixed sort orders nor separators.
If you want to aggregate your data by month, your software provides several powerful tools that do not depend on fixed sort orders nor separators.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How do I add a row when the data changes in LibreOffice
If you need to do this just once, you can increase the efficiency of doing it manually by putting a formula like
in an adjacent column and then filter that column for values of 1. You can then choose each of those rows and insert a row above it.
Formatting your data with gaps in it is often a bad idea because it makes the data harder to process. Could you explain what your goal is?
Code: Select all
=IF(MONTH(A3) <> MONTH(A2);1;0)
Formatting your data with gaps in it is often a bad idea because it makes the data harder to process. Could you explain what your goal is?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: How do I add a row when the data changes in LibreOffice
Cross posted to AskLibreOffice
Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 25.2.4.3; SlackBuild for 25.2.4 by Eric Hameleers
---------------------
Apache OpenOffice 4.1.15
LibreOffice 25.2.4.3; SlackBuild for 25.2.4 by Eric Hameleers
---------------------
Roses are Red, Violets are Blue]
Unexpected '{' on line 32
.-
- Posts: 4
- Joined: Thu Apr 16, 2020 4:15 pm
Re: How do I add a row when the data changes in LibreOffice
Aggregating the data by month is not I am trying to achieve. In fact the data I have provided here is just an example. What I want to achieve is when there is a data change, disregard of the content, I want to add one blank row. Thank you.Villeroy wrote:Whatever you try to do -- BULLSHIT. Don't do that!
If you want to aggregate your data by month, your software provides several powerful tools that do not depend on fixed sort orders nor separators.
Pankaj Trivedi
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
-
- Posts: 4
- Joined: Thu Apr 16, 2020 4:15 pm
Re: How do I add a row when the data changes in LibreOffice
I appreciate your response and I will try to see if this helps. Basically I have considerably large data consisting with a number of companies and I am trying to separate each company with a blank row.FJCC wrote:If you need to do this just once, you can increase the efficiency of doing it manually by putting a formula likein an adjacent column and then filter that column for values of 1. You can then choose each of those rows and insert a row above it.Code: Select all
=IF(MONTH(A3) <> MONTH(A2);1;0)
Formatting your data with gaps in it is often a bad idea because it makes the data harder to process. Could you explain what your goal is?
Pankaj Trivedi
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
Re: How do I add a row when the data changes in LibreOffice
Well, this information was missing in the original question. It somehow better regards the rule: "Always tell what you eventually try to achieve." - And it should be given initially to avoid time-wasting on behalf of all the participants in the thread.Pankaj2006 wrote:Aggregating the data by month is not I am trying to achieve. In fact the data I have provided here is just an example. What I want to achieve is when there is a data change, disregard of the content, I want to add one blank row. Thank you.
However, that's still not clear enough due to the fact that you tell (one aspect of) what you not try to achieve, but still miss what you try to achieve under the functional aspect. In addition "...when there is a data change, disregard of the content..." surely is insufficient since your own example doesn't insert a row at any data change, but only when the change is relevant in a specific sense (the month in your example).
Spreadsheets aren't made for fancy structural layouts. They are made for achieving functionality. Most volunteers here therefore prefer to know about the intended functionality before they start taking the time to consider an answer. So do I.
You now should understand that experienced contributors to this forum may suspect you to be rather unexperienced - and should be told some principles.
Chancing to be wrong I dare to guess: You want to clearly emphasize the start of a new sub-sequence of values being grouped under a specific aspect. Borrowing your example with dates I offer a solution by the tool appropriate for many kinds of emphasizing. That's ConditionalFormatting (CF).
Insertion of empty rows without an inescapable need is evil. It always will destroy the structure under functional aspects.
And: It only can be automated with the help of user code. To suggest a solution this way would surely require to need more details.
The following example was made with LibO 6.4.3 but last saved with AOO 4.1.7.
(Automatic OptimalHeight seems to be buggy in LibO currently to me.)
On Windows 10: LibreOffice 25.2.4 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: How do I add a row when the data changes in LibreOffice
The logical way to do this:
As far as I have tested, it works fine when applied to existing, otherwise unformatted cells. Alas, this fails badly with entry of new data or when deleting rows, and I suspect that it will also easily fail when other cell formatting (for other cells in each row) is in place. Reapplying the conditional format fixed the grouping again in my tests.
If this does not solve the issue, post a sample file with your actual data and existing formatting. I (and others, I suspect) will have a go at it.
If you decide to submit a file, remember to remove/mangle any confidential information in the file. This is a public place.
- Create a cell style for row spacing (e.g. named "SpaceBelow"), with added padding below cell content.
- Apply conditional formatting, where the SpaceBelow style is applied when formula MONTH(thisrow)<>MONTH(nextrow) evaluates to TRUE. (The "thisrow" and "nextrow" items must of course be replaced by proper cell references.)
As far as I have tested, it works fine when applied to existing, otherwise unformatted cells. Alas, this fails badly with entry of new data or when deleting rows, and I suspect that it will also easily fail when other cell formatting (for other cells in each row) is in place. Reapplying the conditional format fixed the grouping again in my tests.
If this does not solve the issue, post a sample file with your actual data and existing formatting. I (and others, I suspect) will have a go at it.
If you decide to submit a file, remember to remove/mangle any confidential information in the file. This is a public place.
Last edited by keme on Fri Apr 17, 2020 1:50 pm, edited 2 times in total.
-
- Posts: 4
- Joined: Thu Apr 16, 2020 4:15 pm
Re: How do I add a row when the data changes in LibreOffice
Hi,
My apology if my query has, in any way, wasted the time of the members.
I thought my question was explicit and anybody reading it would understand it. To make it easier I gave an example, but yet this has misled the readers.
No worry. I am marking this query as closed. Thank you very much for your assistance.
If admin guys wish, they can ban me forever.
Pankaj
My apology if my query has, in any way, wasted the time of the members.
I thought my question was explicit and anybody reading it would understand it. To make it easier I gave an example, but yet this has misled the readers.
No worry. I am marking this query as closed. Thank you very much for your assistance.
If admin guys wish, they can ban me forever.
Pankaj
Pankaj Trivedi
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
Re: How do I add a row when the data changes in LibreOffice
Apologies not required, and banning is not an appropriate action.Pankaj2006 wrote:...
My apology if my query has, in any way, wasted the time of the members.
...
If admin guys wish, they can ban me forever.
...
As Lupp explained, we observe flaws in your posting (and also in your strategy when working with the spreadsheet tool), and typically, helpers here will point out such flaws. No offense intended. No aggression involved (or perhaps just a little bit

Albeit sometimes tersely formed, the responses here are usually friendly and intended to help. Reading with this in mind, you may find that you have been given some resources (solutions, as well as strategies).
Also, as I stated in my previous answer, please post back if your issue persists. An actual spreadsheet file containing the data you are working with is much more useful than lines of text made to look like spreadsheet content. It gives us the actual material you are working with, which relieves us from recreating a dataset, and thereby also relieves us from guessing about the properties (data type, formatting, etc.) of each data element. Help us to help you!
Re: How do I add a row when the data changes in LibreOffice
I still don't get what you are after.
---------------------------
Row insertion in order to expand a list is a good idea, indeed.
Let's assume you have some list in cell range A2:F99 with column labels in the top row A1:F1.
Then you have several formulas like MIN(D2:D99), MAX(D2:D99), AVERAGE(B1:B99) or SUM(F2:F99).
Now you want to add a new record to your list. If you simply write the record into row #100, the above formulas will not follow. They will refer to rows #2 until row #99. However, after insertion of a new row all formulas will refer to rows #2 until #100: MIN(D2:D100), MAX(D2:D100), AVERAGE(B1:B100) or SUM(F2:F100). Same with any charts, named references and all the features that are based on formula expressions referencing the affected range with the newly inserted row(s).
If this effect does not take place after inserting a new row directly below the actual list range, then you should go to menu:Tools>Options... Calc>General and check the option to "expand references when new rows or columns are inserted". With this option checked, any rows inserted directly below or on top of a referenced range will also expand all references to that range. When this option is unchecked, insertion on top would move down the range. SUM(A2:A99) would turn into SUM(A3:A100). And any insertion directly below would have no effect on the adjacent references.
--------------------------
Some people solve this typical spreadsheet problem with formulas like MIN(D:D), MAX(D:D), AVERAGE(B:B) or SUM(F:F) which refer to the entire columns. This has several draw backs.
1) You must not enter any data below the actual list that do not belong to that list, for instance some scratch pad calculations or accidental input because the formulas include these unintended extra values.
2) The formulas must not be placed within those columns because this would be a circular reference.
3) These formulas inevitably include the first row of column labels which may matter in some cases, for instance with COUNTA(A:A) which counts every value, text and numbers.
4) OpenOffice does not support this formula syntax. You have to enter MIN(D1:D1048576) and this range will shrink every time when you delete some rows.
--------------------------
Separating one list by means of blank rows or columns just for visual appearance is a very bad idea because a lot of features work much better with adjacent cell ranges. If visual appearance of data groups really matters, database reports are the way to go
---------------------------
Row insertion in order to expand a list is a good idea, indeed.
Let's assume you have some list in cell range A2:F99 with column labels in the top row A1:F1.
Then you have several formulas like MIN(D2:D99), MAX(D2:D99), AVERAGE(B1:B99) or SUM(F2:F99).
Now you want to add a new record to your list. If you simply write the record into row #100, the above formulas will not follow. They will refer to rows #2 until row #99. However, after insertion of a new row all formulas will refer to rows #2 until #100: MIN(D2:D100), MAX(D2:D100), AVERAGE(B1:B100) or SUM(F2:F100). Same with any charts, named references and all the features that are based on formula expressions referencing the affected range with the newly inserted row(s).
If this effect does not take place after inserting a new row directly below the actual list range, then you should go to menu:Tools>Options... Calc>General and check the option to "expand references when new rows or columns are inserted". With this option checked, any rows inserted directly below or on top of a referenced range will also expand all references to that range. When this option is unchecked, insertion on top would move down the range. SUM(A2:A99) would turn into SUM(A3:A100). And any insertion directly below would have no effect on the adjacent references.
--------------------------
Some people solve this typical spreadsheet problem with formulas like MIN(D:D), MAX(D:D), AVERAGE(B:B) or SUM(F:F) which refer to the entire columns. This has several draw backs.
1) You must not enter any data below the actual list that do not belong to that list, for instance some scratch pad calculations or accidental input because the formulas include these unintended extra values.
2) The formulas must not be placed within those columns because this would be a circular reference.
3) These formulas inevitably include the first row of column labels which may matter in some cases, for instance with COUNTA(A:A) which counts every value, text and numbers.
4) OpenOffice does not support this formula syntax. You have to enter MIN(D1:D1048576) and this range will shrink every time when you delete some rows.
--------------------------
Separating one list by means of blank rows or columns just for visual appearance is a very bad idea because a lot of features work much better with adjacent cell ranges. If visual appearance of data groups really matters, database reports are the way to go
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice