[Solved] Insert a blank row after a group of data

[BrianJones]

Hi

I hope that someone here can help me with this. I need to create a Macro which achieves the following:-


I have data laid out in columns as follows:-


Column A B C

00048-00479-03 5038600136329 EAN
00048-00479-04 5038600136336 EAN
00048-00479-05 5038600136343 EAN
00048-00480-03 5038600136183 EAN
00048-00480-04 5038600136190 EAN
00048-00480-05 5038600136206 EAN
00048-00480-06 5038600136213 EAN
00048-00481-03 5038600136251 EAN
00048-00481-04 5038600136268 EAN
00048-00481-05 5038600136275 EAN
00048-00481-06 5038600136282 EAN



And I need to insert rows as follows:-


Column A B C

00048-00479-03 5038600136329 EAN
00048-00479-04 5038600136336 EAN
00048-00479-05 5038600136343 EAN

00048-00480-03 5038600136183 EAN
00048-00480-04 5038600136190 EAN
00048-00480-05 5038600136206 EAN
00048-00480-06 5038600136213 EAN

00048-00481-03 5038600136251 EAN
00048-00481-04 5038600136268 EAN
00048-00481-05 5038600136275 EAN
00048-00481-06 5038600136282 EAN


- so that there is a new blank row between each row where the first 11 Characters (5 figures, - , 5 figures) changes.


I have seen a similar Macro for Excel here:-

http://stackoverflow.com/questions/1541 ... up-of-data

However, this does not accomodate the fact that the last two figures in column 'A' will change.

All suggestions gratefully received!

[MrProgrammer]

Hi, and welcome to the forum.

I need to create a Macro which achieves the following …

There's no need for an evil macro. You can do this with built-in Calc features. In D1, put =LEFT(A1;11) and fill the formula down the column. Insert a row before your first data line and create a column label in row 1 by typing a D in D1. Column labels are required for the next step. Data → Subtotals → Group by D → Subtotals for → D → Function → Count → OK. Data → Group and Outline → Remove. Highlight column D (by clicking on the D above cell D1) → Edit → Delete Cells. Delete row 1 if you want, too.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[BrianJones]

Hi MrProgrammer

Thanks very much for that neat solution - and so quickly! Worked perfectly.

I will have to get to grips with the macros though ... so if anyone does have a Macro based version that would also be very useful as a starting point.

[Lupp]

And again no macro ...

As long as nobody gives a clear rationale from another point, I will stick to the interpretation that the empty rows shall simply subdivide the data visually. To do this at the cost of keeping the data themselves contiguous is against well established principles of data keeping. It will surely aggravate evaluations some day. There will always come up needs of maintenance and enhancement ...

Of course, visual markedness is valuable. But I would recommend to achieve it using an extra "PrettySheet" for the purpose and keep the data themselves as functionality requires.

To help understand what I mean, I attach a (rather crudely sketched) example.

(Of course Conditional Formatting might already sufficiently clarify the situation.)

[BrianJones]

Hi Lupp

Again, thank you very much for an interesting and logical solution. To respond to your observations - I will be filling in information in the gaps with information derived from the first row of each separated group - and the object ultimately is to create a macro which performs the separation and then derives the other necessary information to fill in the gaps.

Also, this process will be repeated regularly - say monthly - with sheets of 20,000 rows, so I would like to learn to create a macro so that I can open a file with a standardised set of columns, and run a macro which performs the separation and creation of the extra information for the new blank rows...

Also I will then need to perform similar processes with other data sets, so I am keen to learn...

[Villeroy]

What you intend to do is not the best way to keep your data consistent.
Nevertheless, there is a built-in feature for what you try to do:
Create a column of substrings ("00048-00479" etc.)
Select the whole list and call Data>Subtotals...
Create subtotals for the substrings. They will be inserted into your list as SUBTOTAL formulas.

A pivot table would be a much better solution because it would keep your list intact.

[Lupp]

@BrianJones: Splitting data of common structure and thus possibly (one day) to submit to common evaluation into chunks with intemediary rows, or -even worse- on different sheets is a repeatedly reinvented approach. I did things that way 15 years ago, too, sometimes. Backed by experience I may dare say: It's a bad idea.

Mixing up the keeping and the presentation (printout e.g.) of data isn't a good idea either.

You may, of course, do as you choose. Good advice should not flatter the advised one, however.

Did you consider the implications of relying on custom programming for performing tasks in spreadsheets? There are some! I surely might be able to write a custom program doing what you want. I actually did, out of interest, some steps of it in a crude style. But - programmed "quick and dirty"- it won't be of much value. A thorough finish including valid testing is beyond my capabilities, or, said with other words, would exceed the time I would like to spend. Teaching you about the matter would even more require expertise I don't have at hand ..., and a lot of time.

The formula solution is the best approach I can offer. It can easily be adapted to insert titles or additional information into the presentation sheet. I did such things for real-world-puroses and that worked well enough. On the level of keeping and maintaining the data additional information should be placed in additional columns of the data sheet. And only if 1024 columns are not enough a second sheet with congruent row numbers should be used. (A lookup table sheet might be another means for the purpose.) Having to handle 20'000 rows and many (?) columns on a regular basis might, however, indicate necessary considerations about solving parts of the task outside Calc.

You won't type in your 20'000 rows by hand. They will come from somewhere. To prepare raw data for a specific presentation - or even for an import into Calc sheets may well be a domain of actual programming. But that should be done based on a multi-purpose programmimg language (and IDE). I did such things, too, for some time. But even if I decided to do it with OpenOffice BASIC, I would prefer generating a second sheet and placing data there into calculated positions by my program instead of inserting rows between the original data. No programming without thorough considerations in advance. Violating this principle now and then may be one of the roots of the army of bugs living in our software world.