Hello,
I am trying to filter a table with merged cells
Whenever I filter for only the “BMS” device, it shows me only one row:
Is there a way to filter properly all rows “BMS” is contained?
Version: 7.1.8.1 (x86) / LibreOffice Community
Build ID: e1f30c802c3269a1d052614453f260e49458c82c
CPU threads: 12; OS: Windows 10.0 Build 19043; UI render: Skia/Vulkan; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: threaded
I was not allowed to add multiple images to one message, so I added the original table here:
There are some solutions How to convert merged cells into ordinary cells and copy data from merged cells to ordinary cells?
but they rely on copy paste
Please don’t use the “Suggest a solution” functionality for something else but a solution - or at least an answer
Generally edit your question, or if urgently needed, post a comment to it.
Don’t attach images, but example .odf files showing what you have and whart you want to get. In many cases images attached to forum posts are nearly as evil as merging.
Unmerging cells is very simple with recent versions of calc: Simply “select all” (Ctrl+A mostly) and activate Unmerge cells from the context menu of the selection.
this will make cells empty with no value
This is because the cells to which the filter is applied don’t actually contain the key.
Merged cells are a mess anyway. Avoid merging!
If you do it against my advice, thoroughly make sure that each cell has the same content in advance, and that this content is kept… You get offered a respective option.
The filter works based on the actual content of cells whether they are “merged under” or not. Merging simply doesn’t “merge the cells”, but only increases the view-area of the first one covering the others. Did I already tell that merging is evil?
To help you to better see the advantages of NOT merging, see attached example where conditional formatting is used to get the “visual grouping” effect of merging for the view.
demoAgainstMerging.ods (12.3 KB)
That “sometimes” was placed like a glacial boulder between ranges with “never” in my example was intentional.
It demonstrates an aspect of the mess merging can cause: Use the autofilter for column A to only select “sometimes”, and you will get the correct row, but it will show “never” in that column for the seventh row.
A very basic way to undo the evil if a person already has merged rows is to insert a helper column that unravels the merged values. It isn’t promised to be a universal solution. See the attached example sheet.
FilteringMergedTables.ods (21.6 KB)
