0

Can we have a list of items in an Excel cell, such that the rows can be filtered with Data Filter?

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

enter image description here

?

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:

    enter image description here

    This part is probably not easy.

5
  • Maybe have a cell with a dropdown of possible values to filter on, then link that cell to a helper column and filter on that column Oct 3, 2022 at 21:57
  • @cybernetic.nomad Can you give an example? (maybe in an answer it would be easier)
    – Basj
    Oct 3, 2022 at 22:00
  • If you can have your filter results on another tab (or section of the worksheet) and you are only filtering one item, then use @cybernetic.nomad suggestion combined with the FILTER function. From there you should be able to build multiple item filter.
    – gns100
    Oct 3, 2022 at 23:03
  • @gns100 They’re not going to have access to FILTER() in the tagged Excel versions.
    – Max R
    Oct 4, 2022 at 2:08
  • @Max which minimal version is needed? I might upgrade soon. I kept the good old 2007 because it's super super fast on some of my PCs.
    – Basj
    Oct 4, 2022 at 7:07

3 Answers 3

1

Create a cell with a dropdown of possible values to filter on

enter image description here

Create a helper column with the following formula:

=IF(ISNUMBER(SEARCH(H$2,B2)),"Match","")

enter image description here

Filter on that column:

enter image description here

Sample result:

enter image description here

0

It seems to work with LibreOffice Calc, in one click on the Filter, but then we have to enter the name manually:

enter image description here

0

Highlight the cells in question

Data > Filter

Text Filters > Contains > item4

This works in Excel365

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .