How do I add a row when data is changed?

[Pankaj2006]

Hi,

I am trying to add a row when the data is changed. I am having 1500+ rows on my sheet. For example what my current data 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

And I want to do is as follows:

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

Note: I am using Ubuntu 14.04 and LibreOffice Calc Version: 6.0.7.3

I will appreciate any help on this.

Thank you.

Pankaj Trivedi

[anon73440385]

You mean: How do I add a row automatically if month changes (otherwise you would probable use Right click -> Insert Row Above or Right click -> Insert Row Below)?

[robleyd]

Cross posted to AOO forum where responses have been made

[JohnSUN]

Given the additional information (“Basically I have considerably large data consisting with a number of companies and I am trying to separate each company with a blank row.”) I can offer such a solution.

• Choose Data - Subtotals.

• Select in the “Group by” column with company names

• Check the same column in “Calculate subtotals for”

• You can change the “Use function” to Count - but this is not necessary, you can leave the “Sum”

• Click OK. Сalс will group your data according to the specified criteria.

• Now use Find&Replace (Ctrl+H) to remove formulas SUBTOTAL()

• If you do not need groupings (to the left of the table), press Ctrl+F12 several times

There are other ways to solve your problem - a macro, sorting by an additional column, using a standard filter with moving the filter result to another place…

Update. To see how this can be done with a macro, see the wonderful example that a respected colleague Lupp posted in a comment below.

I will show how the same task is solved with sorting by an additional column

For ease of viewing, textual remarks from the movie are placed here:

• First of all, sort your data by key column
• Now add an index column. In the first cell, put just 1
• This formula will increase the value of the index by 1 for each key change
• Fill the entire column with this formula
• Replace the formula with its value. Just copy the range and paste it as data
• Select all index values without a column heading (Ctrl+Shift+End)
• Using the Standard Filter, select unique index values and add them below the existing ones
• Now sort the table by the Index column
• …and delete it

[Lupp]

The additional information mentioned by @JohnSUN (“Basically I have considerably large data …”) wasn’t posted to this thread, but to one at a different forum where the OQer had asked (basically) the same question - without notice, of course.
@JohnSUN: Did you read the “final” post by @Pankaj2006 in “the other” forum?
This is one of the cases where I come to think of ceasing from contributions to forums.
Unfortrunately the OQer was confronted there with a nasty comment by somebody else.
There still is no attempt to explain for what reason the OQer considers the insertion of additional rows a good idea - or even necessary.
Whether kindly or not some very experienced users had dissuaded from the concept.

[JohnSUN]

@Lupp Yes, I read that topic. And yes - I saw your decision. I agree with you, violating the integrity of a table is a bad idea, formatting is better than empty lines inside data. And I agree with you - a more detailed description of the task gives more chances for a successful solution.

[Lupp]

Though I don’t think there ever is a realistic use-case it was fun to write a bit of code for what I now think the OQer aimed at. Permit macro execution and see second sheet in
aoo101657emphasizeRowsOrInsertConditionally_1.ods
After introducing lots of formulas into the respective sheet it may also serve as a demonstration how things may go wrong. If a row must be inserted at all, it is definitely necessary to thoroughly consider what might happen to formulas and the validity of their results. This not only concerning the sheet where it’s done.
Think alone of the discussion concerning the update-formulas-when-sorting-issue where most users still will not have understood the implications - and where I not am sure if I have. Has Kohei?