For example:
A: aaa, bbb, ccc, ddd, eee
B: fff, bbb, eee
How do I filter the data to get something like this:
A: aaa, ccc, ddd
Thanks.
This is a very oldest trick.
Step 1. Convert data of filter to row with condition “not equal”
Use formula
=TRANSPOSE("<>"&[Range with filter data])
(Complete the enter a formula by pressing Ctrl+Shift+Enter)
Step 2. Repeat the header of data column in each cell under the string of conditions
Step 3. Apply an advanced filter
NB. This trick can be useful only for the case when the values of the filter is less than the number of columns in the worksheet. If these values more, it is better to use a macro. One solution is shown in this discussion.
@JohnSUN, The use of animated GIF is pretty slick! I wish that there were controls or something on there – The engineer in me would want to write a whole new svg-based format, but my hacker side wonders if there’s some extension for firefox that would just provide standard controls for any gif.
Plenty of ‘gotchas’ in these instructions.
- Ctrl+Shift+Enter changes the type of fomula, makes it a matrix formula
- All cells of the row need to be selected before typing in the =TRANSPOSE() formula, otherwise only the first cell contains any data after formula is executed on my version of LibreOffice
- If two rows are not used (e.g. ‘Data’ is not repeated) then the filtering fails with an error Even after all this the data is not filtered (remains unchanged in my case)