Find and keep matches from 2 columns in Excel
If you are doing this every morning that you might want to look at my Duplicate Master addin , http://www.experts-exchange.com/A_2123.html for a readily applied automated solution
- It provides an output column (your query), highlighting, selection and deletion options regardless of data size
- It can run over multiple sheets (if applicable)
- The addin provides functionality for
- case sensitive/case insensitive matching
- ignoring all whitespaces (spaces, line breaks, CHAR(160))
- ignoring non-printing characters
- regular expression text substitutions
- List 1:
A1:A1000
- List 2:
B1:100
In column C
, enter =COUNTIF($A$1:$A$1000,B1) > 0
, and copy down to row 100.
All the TRUE's represent #'s that are in both lists.
You can also do something similar with VLOOKUP.