Ask Your Question

Can lists be generated automatically in Calc? [closed]

asked 2018-02-25 17:16:17 +0200

Astralogic gravatar image

updated 2021-04-22 01:02:17 +0200

Alex Kemp gravatar image

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:







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 flag offensive 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

2 Answers

Sort by » oldest newest most voted

answered 2018-03-02 00:36:59 +0200

Lupp gravatar image

updated 2018-03-02 00:46:48 +0200

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

edit flag offensive delete link 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.

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

answered 2018-03-01 23:27:19 +0200

Gilward Kukel gravatar image

updated 2018-03-02 01:09:16 +0200

  1. Download the attachment: C:\fakepath\listgenerator.ods

  2. Change the file ending from ods to zip.

  3. Extract it.

  4. Download and install Python3 (

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

  6. Double-click on 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.

edit flag offensive delete link more


Shouldn't have to go through all those gyrations.

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

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

I changed the program in the attachment.

Gilward Kukel gravatar imageGilward Kukel ( 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.

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

Question Tools

1 follower


Asked: 2018-02-25 17:16:17 +0200

Seen: 147 times

Last updated: Mar 02 '18