Spreadsheet data checking columns

Question

I am trying to separate datas from columns(A,B&C) based on datas from an index table at columns (E&F)

I need to list only those data from columns A/B/C when column B data has no corresponding data in the index table at column E.

column B  Column E
   1       99
   2       4
   3       14
   4       77
   5       2
   6       
   7

I am trying to get formula that separates just 1,3,5,6&7 as these are not listed in column E. i.e

column A Column B Column C
  ...       1       ...    
            3       
            5       
            6       
            7

Is there any VBA (or inbuilt functions/macros/anything) that already does such work?

I tried to use this "=OFFSET($E$3,MATCH(B3,$E$3:$E$7,0),1))" in column G as testing but it did not work.

I am not getting how to use this. I would appreciate your any expert advice, any VBA, formula etc, please advise!

I don't understand what you want to do, the examples don't explain why 1,3,5,6,7 are chosen — Raystafarian, Sep 13, 2014 at 22:20
1,3,5,6,7 from column B were chosen because these were not in the column E — azizar, Sep 27, 2014 at 17:55

murxo · Accepted Answer · 2014-09-13 23:47:08Z

0

You can filter the table using this formula in Column G

=ISERROR(MATCH(B1;$E$1:$E$7;0))

supposed you have no column captions, otherwise adjust the range

answered Sep 13, 2014 at 23:36

murxo

284 bronze badges

Add a comment |

1 Answer 1