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.

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.

share|improve this question
add comment

migrated from stackoverflow.com Sep 22 '11 at 12:29

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

2 Answers

  • 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.

share|improve this answer
add comment

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
share|improve this answer
    
Looks excellent...m gonna try this.. –  Confused Oct 31 '11 at 11:13
    
@Sangram , cheers - hope it works for you. I'm happy to take your feedback. :) –  brettdj Oct 31 '11 at 12:48
add comment

Your Answer

 
discard

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