With Calc, how to create a list of items in a cell, and be able to data filter this column?

English
#1

Can we have a list of items in a cell, searchable with Data Filter?

Exemple: how to filter by column B and keep only the rows that contain [item4]:

image

? (screenshot made with Excel and Paint to simulate the desired behaviour)

Notes:

  • we could filter column B by “contains text [item4]” but then we have to enter this text manually or copy/paste it - I’m looking for an easier solution with fewer actions/clicks.

  • I have watched this video, which is nearly a solution with “Data Validation”, but finally not, because it only creates a dropdown list with only 1 selected item.

  • Here is a beginning of solution: Implement tags in Excel in seconds

  • if we want it to be useful, it should be possible to filter the rows by items in the list, and the filter should be populated automatically with all the existing items. This part is probably not easy.

#2

You have a database at hand which is a lot more powerful than Excel.
The attached database demo contains tables for persons, animals and things (corresponding to your items). Each person can have one animal but many things (tools).
relations2listboxes.FB.odb (61.6 KB)
Open input form “Persons”, navigate from person to person by means of the navigation (tool-)bar. The yellow part shows the things (tools) belonging to the person.
Open input form “Things”, navigate from thing to thing by means of the navigation (tool-)bar. The red part shows the persons owning the selected thing (tool).
Doing all sorts of calculations is possible in a database. You can also link database data to spreadsheets and do the calculations on sheet.

#3

Nice, I’ll try this! Does it require another software than Calc?

Do you think there’s a solution inside Calc?

#4

You can make visible the Standard Filter button (in the Standard toolbar).
imagen

Add one item tags in column B, then use the Contains condition changing the value. Meanwhile range is not unselected, condition remains the same.

imagen

#5

Thanks. Can you share a demo Calc file?
Does this autopopulate a dropdown list with all the possible single values of items? (e.g. 1a 1b 1c 2a 2b)
So that we don’t have to manually enter the filter value if we have long item names.

#6

The database can do all this and a lot more.

#7

Yes.

Copy the cells B2:B4 in your sample file, paste in Writer, Find and Replace (space for \n, with regular expressions) so each item comes to be in one line, and paste this in Calc.

#8

Copy the cells B2:B4 in your sample file, paste in Writer, Find and Replace (space for \n, with regular expressions) so each item comes to be in one line, and paste this in Calc.

I don’t understand exactly. Should the cells be multiline ? with one line per item in a single cell ? I tried this.

Maybe a very small calc file would be great to explain your solution :slight_smile:

#9

82547.ods (14.2 KB)

1 Like
#10

Thanks! I wonder if it’s possible without a secondary helper table in the same sheet or without a secondary helper sheet.

BTW, how to display the code you used? I don’t find formulas in the cells @Villeroy and also no macro ; how to display the code you used? Thanks

#11

It is a 30 year old Excel feature called “Advanced Filtering” reading filter criteria from a cell range.
Please do us all a favour and use Excel if this is not good enough. There is no Excel clone.

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