Filter out items in column B from column A

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

An animated example of the solution

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.

  1. Ctrl+Shift+Enter changes the type of fomula, makes it a matrix formula
  2. 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
  3. 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)