I am trying to use the "Search items" box in the AutoFilter dropdown to show several items with separate searches. However, I can only get it to select the latest item I searched for. Is there a way to use "Search items" sequentially to add multiple items to the filter?
What I am doing:
I have a spreadsheet with 1,000 or more rows. I want to apply a filter which hides most of the rows, but shows a small set. This set cannot be found with a single search in the "Search items" box. I use the AutoFilter dropdown on one column to uncheck "All", then I type into the "Search items" box to find one of the ones I want. After typing its name into the box, the item I want shows up in the dropdown, and it's already selected. I press "OK".
Now I want to add another item, so I click on the dropdown, and type the second name into "Search items". It appears, and is already selected. I press "OK". However, my first item has disappeared - it is no longer selected in the AutoFilter.
I can get to the desired end result by skipping "Search items", and repeatedly scrolling through the list to find each item. This works, but it's relatively laborious. What if there were 10,000 rows, or 100,000 rows?
One possible workaround is to type the names I want into a second sheet, then use multiple VLOOKUPs to populate those rows with the data from the first sheet, but again this is relatively laborious.
Another workaround is to add an extra column for filtering. By repeatedly using "Search items" in the AutoFilter to show one row, it's possible to populate the new column with suitable values, and then use AutoFilter on the new column to filter out the blanks. This works, and is probably the best way forward for now.
Is it possible to add together the filter selections from several "Search items" queries in the AutoFilter?
I am using version 7.4.1.2 / LibreOffice Community.