1

In Excel I have two columns, one with about 3000 rows and one with about 100. I need to find all of the matches between the two columns and put them in a separate column. I have to do this every morning so if there is a way to do so, I'd like to make it as automated as possible.

Improve this question

2 Answers 2

Reset to default
2

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

  1. It provides an output column (your query), highlighting, selection and deletion options regardless of data size
  2. It can run over multiple sheets (if applicable)
  3. 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
Improve this answer
2
  • Looks excellent...m gonna try this..
    – Sangram
    Oct 31, 2011 at 11:13
  • @Sangram , cheers - hope it works for you. I'm happy to take your feedback. :)
    – brettdj
    Oct 31, 2011 at 12:48
2
  • 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.

Improve this answer

You must log in to answer this question.