Wednesday, February 15, 2012

yogi_Mark Items in LIST1 That Are In LIST2


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

user jveix33 said:
Compare Column B to Column A, Highlight Duplicates
Hello,
I am working on two different databases with some overlapping data. Currently I have two columns, Column A has a list of everything, and Column B contains a list of things I've already added from Column A. So basically I was wondering if anyone could help me find a way to compare column B to column A and highlight the duplicates in column A therefore I can see clearly what I still have to add. The Problem I've been having is that each column has a different number of items and this was giving me trouble developing a formula.
Thanks.
John
------------------------------------------
following is a solution to the problem

2 comments:

  1. Hello! Awesome post, so helpful...except for some reason it is not working for me. I am trying to compares values listed in one column to another column, and somehow identify the duplicates. For some reason, when I use your formula, it never says Yes. :( Would it have something to do with the fact that my values contain decimals? I can't seem to attach an image...but can you explain how the formula works so I can troubleshoot it?

    ReplyDelete
  2. Hi SnailTwist:

    The formula in cell A2 looks for exact match for items in range B2:B with those in range C2:C and if there is a match, it marks Yes. The formula in cell A2 should work for all entries --whether the items are Text or Numeric (with or without decimal point)-- for illustration I have added Sheet1a where I have used both Text and Numeric entries.

    I hope this helps.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete