2

What I want to do

Find Items in one column (ColA) that are not in another column (ColB). What to do when I want the result not being highlighted, but have it in another column (ColC) without blank rows.

Example

ColA - ColB - ColC    
1 - 1 - 4
3 - 2 - 8
10 - 3 - 10
4 - 5 - "" 
5 - 7 - ""    
8 - 6 - ""
9 - 9 - ""

What did I try yet

Until now I succeeded to have the following result. I do this with the following formula in column C:

=IF(IFERROR(MATCH(A2;B$2:F$300;0);"")<>"";"";A2) 

Result:

ColA - ColB - ColC
1 - 1 - ""
3 - 2 - ""
10 - 3 - 10
4 - 5 - 4     
5 - 7 - "" 
8 - 6 - 8
9 - 9 - ""

But I want to avoid the blank cells in Col C.

I tried the formula I found in one of the answers on this site:

IFERROR(INDEX($A$2:$A$1999,MATCH(0,IFERROR(MATCH($A$2:$A$1999,$B$2:$B$399,0),COUNTIF($C$1:$C1,$A$2:$A$1999)),0)),"")

… but this does not work. Maybe I adapt this formula in a wrong way...?

1 Answer 1

1

You will have to use an array formula. Assuming your data in ColA and ColB is in rows 1 to 10, select rows 1 to 10 of ColC, then input the following formula in the formula bar:

=IFERROR(SMALL(IF(ISNA(MATCH(A1:A10,B1:B10,0)),A1:A10,""),ROW()),"")

Store it as an array formula by pressing Ctrl+Shift+Enter (on Windows) / Cmd+Shift+Enter (on OS X) – if you did it right, the formula will display enclosed in braces ({}) and look identical in all cells. As applied to your example data, this is what you get:

screenshot of the formula used on the posted example

Note there are a few gotchas when working with array formulas (they can only be edited by editing the whole range, cannot be shrunk, can only be expanded by manually editing the formula … see the article I linked to for some details, section “Rules for entering and changing array formulas”).

2
  • Thanks for your clear explanation, but it does not work. What am I doing wrong ? First I had to adapt the formula, since the ',' is not accepted, it should be a ';' on my computer. So it became : =IFERROR(SMALL(IF(ISNA(MATCH(A1:A10;B1:B10;0));A1:A10;"");ROW());"") Then I selected the cells C1->C10 and I pasted the formula in it + afterwards ctrl+shift+enter. The result in all cells=0 The formula in C2 =IFERROR(SMALL(IF(ISNA(MATCH(A2:A11;B2:B11;0));A2:A11;"");ROW());"") So no {} in or around the formula. Can it be that Ctrl+Shift+Enter should be something else on my computer?
    – Marleen
    Oct 31, 2011 at 15:37
  • No, just a minor misunderstanding as to execution to clear up :). To get the formula to work, delete the formulas in C1:C10, select the cells again (as one selection area), then insert the formula into the formula input bar (that is the field above the table headers where you can call up the formula assistant) – do not paste directly into the sheet! Then hit Ctrl+Shift+Enter and you should be good to go.
    – kopischke
    Oct 31, 2011 at 16:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .