Libre Basic Grouping Ranges and Sum them

[Mazar]

Hi together,

I am completely new to Libre macros and I have a problem, which I have really no clue how to do this.

I have a sheet with column A and different numbers each row. Now I want to group all number starting with 1 in one array or named range and all numbers starting with 2 and so on. After this I want to insert a row after every group, starting with the group name and sum of several columns. Its hard for me to explain, so here are 2 screens with the starting scenario and end scenario.

I really do not know if this is possible to do with Macros

Thank you very much for your help

[Lupp]

Grouping is a different thing, You want to sort. You do not want to sort just the numbers, but the rows by the numbers contained in column A, obviously.
What do you mean by “…different numbers each row…”?
“…all number starting with 1 in…” Do you mean the sorted rows should start with 1 in columns A? If so, do you assure that a number 1 actually occurs in column A? Or do you (strangely) want to sort by the first decinmal digit of the numbers in column A?

Yo may probably better tell what you evantually want to achieve by your supposed macro?
Interstratified rows in spreadsheets are most ly a bad idea.

Why must it be done with a macro?

[Mazar]

Sorry for the confusion. The example was incorrect, in column A there are numbers like 1237, 1268, 2017, 3012, 1398 and I want to sort them by the first digit in the number.
I want a macro for this because this list is coming from our accounting software and this list is sorted incorrectly. The software sorts and sums by ranges of numbers, so the range 1000 - 1999 is summed just like the range 2000-2999 and so on.But also the range 10000 - 19999 and here is the error I need a sum from the ranges 1000 - 1999 and 10000-19999. Since we generate this list every month, here would be a macro helpful to not do this manually every month.

And I would say named ranges would be the correct way, because all the ranges are account types, for example range 1000-1999 and 10000-19999 would be investment and capital accounts.

[Lupp]

Well, meanwhile I made a demo which somehow is debased now.
To rework it better meeting your needs, I would want to know if the “numbers” in cilumn A are actually numbers by type or texts looking like numbers.
Anyway I would prefer a solution without “macros”… This trhe more as you not seem to be experienced enough, to maintain the solution if a few details of the preconditions change.
In addition: Never set a macro on the original data if you not are absolutely sure it won’t spoil them.
(Rethought: I can dispense the type question as long as not leading zeroes created by the NumberFormat can occur.)

[Mazar]

Ich sehe, dass du auch aus Deutschland kommst, vielleicht kann ich es so besser erklären, wenn das hier okay ist.
Warum ich dies gerne mit einem macro lösen möchte, ist der Punkt, dass die Liste unsere Buchhaltung generiert und ich eine Möglichkeit finden möchten, dass die Kollegen diese Liste immer automatisch anpassen können, ohne immer nach Hilfe fragen zu müssen. Ich bin zwar Programmierer, hatte aber noch nie mit etwas in dieser Richtung zu tun.
Die Werte in Spalte A sind Zahlen und kein Text. Meine Idee war es, Named Ranges zu erstellen, mit den entsprechenden Zahlenbereichen, unter jede Range eine leere Zeile einzufügen und dann pro Range die benötigten Summen zu berechnen. Alles natürlich nur eine Idee oder Vorstellung, ob das überhaupt machbar ist, konnte ich so noch nicht herausfinden

[Lupp]

To the native speakers of English: Apologies for this digression in German.
Ich programmiere ganz gern “spielerisch” oder “aus Neugier” mit der LibO API (wie du anderen Antworten von mir entnehmen kannst:
Her bleibe ich dabei,
-1- dass “named ranges” keine gute idee sind,
-2- dass intermittierende Reihen (Zeilen) keine gute Idee sind,
-3- das -wenn überhaupt verwendet- ein Makro nur das Kopieren und Sortieren der OriginalDaten ins Auswertungsblatt erledigen sollte.

I can’t teach efficiently enough the LibO API to a newcomer to enable him to do the programming job on his own, and I don’t like to use a lot of time to do it for him. This in specific as I can’t expect to get a good solution this way. I simply dislike bad slutions too much.
To support an autodidactic approach I only can point to the famous texts by Andrew Pitonyak.

[Lupp]

Nach all den Kommentaren und Versuchen eine simple Frage:
Warum tut eigentlich eine PivotTable (.DataPilotTable) nicht, was du brauchst?
_
Generally the kind of sorting, grouping, and calculating sums by groups looks like an SQL / database task. Any attempt to do it with the help of macros in Calc will be a kind of reinventing the wheel, but a poor and vulnerable wheel in place of what is standard in the appropriate software.
The next enhancement of the solution will look as if lots of new code are needed, while an additional SQL statement would do it if a database is used.
Calc (and different spreadsheet software) offer some reduced DB functionality, and the PivotTable is the most relevant one, probably.

[Mazar]

Hi, danke für die Hilfe. Ja eine Pivot Tabelle könnte auch helfen.Das Problem ist einfach, dass unsere Buchhaltung jeden Monat eine neue Liste generiert und wir möchten nicht, dass die Kollegen wegen dieser felerhaften Liste noch manuell viel bearbeiten müssen.
Aktuell bin ich soweit, das ich verschiedene Ranges definiert habe 1000-1999, 2000-2999 und so weiter, diese fasse ich nun zusammen und kann zu jeder Range die Summer bilden die ich dann in einer neuen Teile unter jeder Range aufführe. Was ich nun noch herausfinden muss ist wie ich diese Ragnes kopiere und neu geordnet auf ein neues Sheet bringe

Meine zweite lösung wäre auch gewesen ein Skript zu schreiben, welches die Daten der Liste in eine Datenbank importiert und ein weiteres Skrip welches dann die Daten wunschgemäß bearbeitet.

Ich wollte jetzt aber alles vorerst innerhalb Libre ausprobieren.

Das Hauptproblem ist einfach das unser Buchhaltunsprogramm die Konten 1000-1999 und 10000-19999 trennt und getrennt summiert.

[Lupp]

Probably you find some ideas here:
ask319980sortingAndSummingByContentOfaColumn_4.ods

[Lupp]

The attached spreadsheet document is showing how I would solve a task like the one I suppose behind your question:
The first sheet has the raw data (generated randomly).
The second sheet was create as a copy of the first one.
There the rows were sorted using columns A as the only sortkey.
The formulas in coulumns F through I are doing the relevant work.
If data are edited in or appended to the first sheet, you only need to copy the changed data columns to the second sheet and sort them there anew.
Once in a while you may also need to copy the working formulas farther down. That’s all.

ask319980sortingAndSummingByContentOfaColumn_1.ods

New attachment regarding the additional information given by the OQ in his comment on the question.:
ask319980sortingAndSummingByContentOfaColumn_2.ods

[Edit 2021-07-27 about 16:35 GMT]
Just for fun now also with macro for the CopyData and Sort steps:
ask319980sortingAndSummingByContentOfaColumn_3.ods
[/Edit]