Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

So I have two columns in Excel with these values suppose:

Column A | Column B
---------|---------
12       | 626
241      | 129
35       | 412
412      | 88
532      | 663
626      |
723      |
88       |
932      |

What formula should I use to delete the values 88, 626 and 412 from Column A (applying the formula then going to Goto > Goto Special > Errors... and deleting rows)

Can't quite get this sorted at all!

share|improve this question

migrated from stackoverflow.com Dec 16 '12 at 17:43

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers

Sometimes (your case for example), errors may be useful. There's function NA(), which will display #N/A error in a cell. Combining it with IF and COUNTIF will give the desired result.

share|improve this answer
add comment

Enter into C1, and copy down to C2:C9.

=IF(COUNTIF(B:B,A1)>0, NA())

If value of A1 is found in column B:B, show #N/A in C1.

And then "applying the formula then going to Goto > Goto Special > Errors... and deleting rows".

share|improve this answer
    
The delete rows method (as mentioned by OP!) may delete data in ColumnB whose retention may be desired. –  pnuts Dec 18 '12 at 22:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.