# Can lists be generated automatically in Calc? [closed]

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.

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2021-04-22 00:46:51.759498

Sort by » oldest newest most voted

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.

more

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.

( 2021-04-22 01:36:23 +0200 )edit

2. Change the file ending from ods to zip.

3. Extract it.

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.

more

Shouldn't have to go through all those gyrations.

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

( 2018-03-02 01:29:30 +0200 )edit

I changed the program in the attachment.

( 2018-03-02 01:51:26 +0200 )edit

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.

( 2018-03-02 02:20:22 +0200 )edit