2
0
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...?
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 – 2011-10-31T15:37:52.823
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 – 2011-10-31T16:59:07.733