Filter out items in column B from column A

Question

asked 2012-07-12 10:01:15 +0200

corx_d_afikolami
11 ● 1 ● 2

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.

Accepted Answer

answered 2012-07-12 21:50:06 +0200

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 ( 2012-07-13 08:30:30 +0200 )edit
@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.
qubit ( 2013-01-28 22:47:18 +0200 )edit

1 Answer