Eliminating non-matching cells

[gary]

How do I eliminate the cells in COL A whose contents do NOT match the
contents of the cells in COL C. (Note: For the matching cells, I also
need
to know the contents of the cells in COL B).

A B C

101170041-1 101180013-7
101170042-2 Y 101200001-7
101170043-3 101200004-0
101180013-7 A 101200005-1
101180014-8 Y 101200006-2
101180015-9 Y 101200007-3
101180016-0 C 101200008-4
101180017-1 C 101200009-5
101180018-2 C
101180020-3 Y
101180021-4 C
101180033-5 C
101190029-3 Y
101190034-7 Y
101190048-0
101190060-0 Y
101190064-4
101200001-7 Y
101200004-0 Y
101200005-1 Y
101200006-2 Y
101200007-3 Y
101200008-4 A
101200009-5 Y
101200010-5 Y
101200011-6 Y
101200012-7 Y
101210001-8 C
101210003-0 A
101210004-1 A
101210010-6 A
101210011-7 A

 

[Bernie Deitrick]

Insert a new column A, then use this formula in the new cell A2 (assuming you have headers in row 1)

=ISERROR(MATCH(B2,D,False))

and copy down. Then select columns A through C, sort based on column A, then select all the cells
in A:C where column A is TRUE and delete them. Then delete your column A of formulas, and you're
done.

HTH,
Bernie
MS Excel MVP

 

[gary]

Hi Bernie,

How far down do I copy the formula? (To the last row containing data
in COL B or in COL D).


Gay

 

[Bernie Deitrick]

Gary,

atch the length of column B. Also, it is a good idea to do the operation on a copy of your file,
and then check to make sure that the correct items are being removed before proceeding.

HTH,
Bernie
MS Excel MVP