How do I add a row when the data changes in LibreOffice Calc

Discuss the spreadsheet application
Post Reply
Pankaj2006
Posts: 4
Joined: Thu Apr 16, 2020 4:15 pm

How do I add a row when the data changes in LibreOffice Calc

Post by Pankaj2006 »

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

Re: How do I add a row when the data changes in LibreOffice

Post by Zizi64 »

I am trying to add a row when the cell data changes.
You must WRITE a macro to achieve this task.
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
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How do I add a row when the data changes in LibreOffice

Post by Villeroy »

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.
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
FJCC
Moderator
Posts: 9549
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How do I add a row when the data changes in LibreOffice

Post by FJCC »

If you need to do this just once, you can increase the efficiency of doing it manually by putting a formula like

Code: Select all

=IF(MONTH(A3) <> MONTH(A2);1;0)
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?
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.
User avatar
robleyd
Moderator
Posts: 5383
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How do I add a row when the data changes in LibreOffice

Post by robleyd »

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
---------------------
Roses are Red, Violets are Blue]
Unexpected '{' on line 32
.
Pankaj2006
Posts: 4
Joined: Thu Apr 16, 2020 4:15 pm

Re: How do I add a row when the data changes in LibreOffice

Post by Pankaj2006 »

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.
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.
Pankaj Trivedi
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
Pankaj2006
Posts: 4
Joined: Thu Apr 16, 2020 4:15 pm

Re: How do I add a row when the data changes in LibreOffice

Post by Pankaj2006 »

FJCC wrote:If you need to do this just once, you can increase the efficiency of doing it manually by putting a formula like

Code: Select all

=IF(MONTH(A3) <> MONTH(A2);1;0)
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?
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.
Pankaj Trivedi
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
User avatar
Lupp
Volunteer
Posts: 3693
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How do I add a row when the data changes in LibreOffice

Post by Lupp »

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.
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.

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.)
aoo101657emphasizeNewYearNewMonth_1.ods
(32.35 KiB) Downloaded 270 times
On Windows 10: LibreOffice 25.2.4 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
keme
Volunteer
Posts: 3781
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: How do I add a row when the data changes in LibreOffice

Post by keme »

The logical way to do this:
  • 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.)
This will add spacing between data items, grouped by month, without adding a spreadsheet row. This is an alternative similar to Lupp's approach. The advantage of mine is that all items will appear the same. The disadvantage is that rendering of this is not entirely reliable when data changes (applying padding dynamically does not always work as intended).

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.
Pankaj2006
Posts: 4
Joined: Thu Apr 16, 2020 4:15 pm

Re: How do I add a row when the data changes in LibreOffice

Post by Pankaj2006 »

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
Pankaj Trivedi
OpenOffice LibreOffice Calc Version: 6.0.7.3 on Ubuntu 18.04.4
User avatar
keme
Volunteer
Posts: 3781
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: How do I add a row when the data changes in LibreOffice

Post by keme »

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.
...
Apologies not required, and banning is not an appropriate action.

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 ;) ). Remember also that for many helpers here (perhaps the majority), English is not our first language.

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

Re: How do I add a row when the data changes in LibreOffice

Post by Villeroy »

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
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
Post Reply