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.
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.