Ask Your Question

Filter out items in column B from column A [closed]

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

corx_d_afikolami gravatar image

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


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-30 20:42:12.821311

1 Answer

Sort by » oldest newest most voted

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

JohnSUN gravatar image

updated 2013-04-25 07:07:02 +0100

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

edit flag offensive delete link more


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 gravatar imageJohnSUN ( 2012-07-13 08:30:30 +0100 )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 gravatar imagequbit ( 2013-01-28 22:47:18 +0100 )edit

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)
samtuke1 gravatar imagesamtuke1 ( 2018-02-20 16:23:21 +0100 )edit

Question Tools


Asked: 2012-07-12 10:01:15 +0100

Seen: 4,001 times

Last updated: Apr 25 '13