Can lists be generated automatically in Calc?

[Astralogic]

Hi, after I have filled a database with data, I have to manually compile a list in the following manner.

Let’s say I have a small example database like so:

TEXT1 1 2 3 4 5 6 7 8 9

TEXT2 9 8 7 6 5 4 3 2 1

TEXT3 8 5 6 4 5 6 9 8 8

The spaces represents individual cells. So TEXT1 is in a cell, then each number is in it’s own cell. From that I will compile lists manually such as:

1
TEXT1
TEXT2

2
TEXT1
TEXT2

3
TEXT1
TEXT2

4
TEXT1
TEXT2
TEXT3

5
TEXT1
TEXT2
TEXT3

etc…

For 1, TEXT1 and TEXT2 are listed because those rows contain a 1. TEXT3 doesn’t so its not listed under 1.
For 2 and 3, it’s exactly the same. 4 is in all three rows so all the TEXT’s are listed under 4.

It’s very simple, hopefully explained it well.

Can this be automatically done? It takes a lot of time.

[Gilward_Kukel]

1. Download the attachment: listgenerator.ods

2. Change the file ending from ods to zip.

3. Extract it.

4. Download and install Python3 (https://www.python.org/).

5. Copy and paste the data from Calc to the file input.txt in the folder listgenerator. Save input.txt.

6. Double-click on listgenerator.py in the folder listgenerator. The file output.txt is created.

7. Copy and paste from output.txt to Calc. Separator Options: Separated by Tab.

I hope it works.

[Ratslinger]

Shouldn’t have to go through all those gyrations.

Also, saw the edit where nothing was actually changed???

[Gilward_Kukel]

I changed the program in the attachment.

[Ratslinger]

Should always note when you modify question or answer (except possibly spelling error). More often than not it changes the meaning. If the attachment was downloaded initially when posted you wouldn’t know it had been modified. Even your revision history doesn’t show it. Only shows the word ‘attachment:’ as modified.

[Lupp]

If you are fond of “macros”, and accept “bad old BASIC” to work for you, you may also try this attached demo where I apply a few older general-purpose-functions from my toolbox to your problem. This will, of course, only work if you enable macros on loading, or if you copy the contained modules into the ‘Standard’ library of your LibO.

The demo worked for me as expected under LibO V 6.0.2.1.

[Lupp]

Special user code like contained in the above attached example is no longer needed for the task.
Already since V4.4 a usable TEXTJOIN() is implemented, and the REGEX() function available since V6.2 can replace (somehow “bumpy”) the formerly used XTEXTSPLIT() from my box in such a case.
If somebody will ask for it, I will provide a new example.