Value amalgamation for all values in a column in Calc.

English
,
#1

Hello Libre Office Community, I am a new user of Calc and before I get really invested in it I need to know if it’s possible to do something. I have a column that is for exercises that I’ve done in a certain book. I don’t do them sequentially, so one day I did exercises 15 and 62. and entered that on the 1st row in cell 1b. Then on another day I entered 23 and 91 in the second row in cell 2b.

Now what I really want is a way to view all exercises that I’ve ever done sequentially ie 15 23 62 91. If I only enter one value per cell it would be easy. Just sort by that column. But I enter multiple values in each cell. So is there a way to do this? Any advice would be greatly appreciated.

#2

How do you separate the numbers of the exercises in each cell? With spaces? With commas?

Press edit below your question to add more information.

#3

Never use commas when listing numbers. It may cause ambiguities concerning the decimal separator or probably concerning a group separator.
Yes, I know that programming languages don’t worry about that. However, most programmers should know what they are doing.
Anyway, there is no function made for efficient splitting of compound contents. A counterpart like TEXTSPLIT() to the (rather young) TEXTJOIN() is still missing. We have questions of the kind again and again, and all the solutions prove to be crutches if looked at more closely.
An alternative crutch I not yet found in the context can use the new (V 6.2) REGEX() fucnction…
I would see it as another kind of hokum.
See regexAsTextSplit_crutch.ods

#4

Of course, you shouldn’t use one row for more than one execise. Use instead column A for (say) the date, and column B for the exercise-ID, probably column C for the needed time … remaining problems… known errors…
If you follow this principle, it is a very well supported task to sort the exercises by any of the data (date, number, whatever makes sense to sort), and you can choose in every case if you want to sort “in situ” or to deliver the result to a different range.
Using spreadsheets efficiently, and having them to easily produce the wanted results, always is a question of the design - from the first minute.

To really get it, just play in advance with the >Data>Sort... feature and try all its options. To read about sorting in the Calc guide should also be considered an option!

===Editing same day===
The announced attachment:ask268733sortableRecords_3.ods

#5

First thanks for responding to my question. I appreciate what you are saying but I really don’t want to have a separate column for each exercise that I did. That would be way too many columns and I’m not logging the time needed etc.
The way I’m trying to organize it is one column for each book. Then in each cell the exercises done for that book for that day. I want all the exercises done in a specific book to be in one cell, not spread out over different cells. Thanks for trying to help, perhaps someone else knows how to do this if it’s possible. I’m hoping that since you directed me to research those other resources that means there might be a way to it.

#6

“…way too many columns…”
When the emperor told Mozart his new opus had too many notes (was too complicated), the answer was “Sire, it has exactly the notes it needs.”
It’s about rows, not about columns.
And it is not about how many additional data you enter/store/evaluate per exercise. Date and number, that may be enough. The relevant fact in your case is that only data separately entered into cells can be sorted by the efficient means provided with spreadsheets.
And: “One item - one row” is the most basical principle you need to regard to get sufficiently simple and easy solutions in cases where you (mis)use a spreadsheet as a kind of database.
This isn’t just my stubbornly fixed opinion, but a well proven fact.
Rhyming it:
More than one item in a data cell
Is the beginner’s royal way to hell.
If you want to ignore the capabilities and needs concerning spreadsheets, you shouldn’t use them.

#7

I know you’re trying to help and I’m sure you’re right. You sound like someone who’s had a lot of experience with this software. But believe me and try to understand putting each exercise number in it’s own column would be totally impracticable here. I have at least 10 books I might do exercises in on any given day. I might do 5-10 exercises in any one book. So that would mean reserving 10 columns each for 10 books. 10x10= 100 columns. Just for the books. That doesn’t include all the other activities I might do in a day that I want to log. It would be so spread out it would be unreadable and antithetical to the purpose of a spreadsheet being somewhere to have data easily accessible in one place.

#8

… So as knowledgeable as I’m sure you are, a person who is strongly opinionated about strict adherence to best practices may not be exactly what is needed here, as opposed to being flexible to find a creative solution, so I won’t be offended if you don’t respond anymore to this question. And thanks for all your time.
If anyone has been following all this and can think of any kind of work around, anything at all, I’d be wide open to suggestions. Thanks in advance.

#9

I don’t think it’s just (or mainly) about opinions.
I simply cannot tell you an actually relyable and efficient way to split compound information placed in a single cell for an evaluation of the kind you want. Of course, I have user functions for the purpose, but there is neither a specification nor any QA for them beyond my personal estimate - and you would probably not be able to make corrections or enhancements if needed one day.
I will attach a sheet document to my answer. It doesn’t return the numbers to single cells each, but lists them sorted as text.
Please don’t blame me for the suggestion to fill in the data bottom up. (It’s the better way in this case.)

#10

I don’t think it’s just (or mainly) about opinions.
I simply cannot tell you an actually relyable and efficient way to split compound information placed in a single cell for an evaluation of the kind you want. Of course, I have user functions for the purpose, but there is neither a specification nor any QA for them beyond my personal estimate - and you would probably not be able to make corrections or enhancements if needed one day.
I will attach a sheet document to my answer. It doesn’t return the numbers to single cells each, but lists them sorted as text.
Please don’t blame me for the suggestion to fill in the data bottom up. (It’s the better way in this case.)

#11

@Lupp When I came to this forum, a few hours ago, I had no idea if there was going to be some way to do what I wanted like follow step 123 and you’re done! After our discourse I am now 100% confident that it’s not me being a noob about spreadsheets, but that by design there is actually no ‘proper’ way to do this. That peace of mind is in and of itself a big help.
So the two options I have now is looking at the user script you kindly gave me, which would involve the pitfalls you described and also learning how to implement something like that. Also I now notice that the ‘save as’ dialogue in Calc has a CSV format option which gave me an idea for a second option. I’ve got some experience in Autohotkey scripting and it looks pretty easy to pull a list from that kind of file and sort it.
So I guess to wrap it up, I’ve got a couple likely ways to go now and if one doesn’t work I’m sure the other will. Thanks again.

#12

I can hardly understand what you might expect from using csv files and everything.
Du you want to export the sorted list, or do you want to have it at your disposition while using the sheet?
Until now I assumed you asked for the second option.
If so:
If you actually are decided to do it a way I would call “stubborn”, you should nonetheless stick to facilities on board of LibreOffice.
Anyway, your “10 columns per book” is not an option, imo. WHY? If you already insist on entering more than one item into a single cell, the usage of more than one column for the same collection of exercises should surely be obsolete.
If you are ready to resort to any kind of programming, you should use the LibO API.
An example for a single collöection: ask268719sortAsGeorgeBechtelLikesIt.ods.
Of course,you need to enable macro execution to see how it works.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.