1. Welcome! Please take a few seconds to create your free account to post threads, make some friends, remove a few ads while surfing and much more. ClutchFans has been bringing fans together to talk Houston Sports since 1996. Join us!

Excel : Need to compare 2 columns to find different

Discussion in 'BBS Hangout' started by Luckyazn, Jan 23, 2013.

  1. Luckyazn

    Luckyazn Member

    Joined:
    Jun 23, 2003
    Messages:
    4,375
    Likes Received:
    68
    I have two columns on my spreadsheet both with the same ID #, but I need to find the ID # that is not in Column A (725 rows) when comparing to Column B (1048 rows)

    I have tried:

    =IF(COUNTIF(A:A,B2),B2,0)

    but that only gives me the matching/duplicates ones.

    I'm trying to find the reverse of that ... resulting in the different ID #.
     
    #1 Luckyazn, Jan 23, 2013
  2. tallanvor

    tallanvor Member

    Joined:
    Oct 9, 2007
    Messages:
    18,719
    Likes Received:
    11,817
    you want the ID#s that are in column B but not in column A? Do I have that correct?
     
    #2 tallanvor, Jan 23, 2013
  3. Harrisment

    Harrisment Member

    Joined:
    Jun 20, 2001
    Messages:
    15,392
    Likes Received:
    2,158
    Why not just use conditional formatting to identify the unique values?

    Select the two columns, then go to Home-->Conditional Formatting-->Highlight Cell Rules-->Duplicate Values. From there click the dropdown and you can choose to highlight the unique values.
     
    #3 Harrisment, Jan 23, 2013
  4. SwoLy-D

    SwoLy-D Member

    Joined:
    Jul 20, 2001
    Messages:
    37,618
    Likes Received:
    1,456
    #4 SwoLy-D, Jan 23, 2013
    Last edited: Jan 23, 2013
  5. Jugdish

    Jugdish Member

    Joined:
    Mar 27, 2006
    Messages:
    9,081
    Likes Received:
    9,591
    Assuming the data starts in row 2, put this in cell C2 and drag down:

    =IF(ISERROR(VLOOKUP(B2,$A:$B,2,FALSE)),"X","")

    Each X that appears identifies a value in column B that is not in column A.
     
    #5 Jugdish, Jan 23, 2013
  6. Haymitch

    Haymitch Custom Title

    Joined:
    Dec 22, 2005
    Messages:
    28,371
    Likes Received:
    24,021
    So here's an embarrassingly easy one: I have one long column of dates and need the formula to make another column of dates 3 months before the date listed in the same same row as the original column.

    [​IMG]

    So basically have column A display a date 3 months before column B
     
    #6 Haymitch, Jun 13, 2013
  7. HR Dept

    HR Dept Member

    Joined:
    May 31, 2012
    Messages:
    6,792
    Likes Received:
    1,223
    Yeah, easy:

    =THISDATE-(THREEMONTHAGO)
     
    #7 HR Dept, Jun 13, 2013
  8. Haymitch

    Haymitch Custom Title

    Joined:
    Dec 22, 2005
    Messages:
    28,371
    Likes Received:
    24,021
    =(B3)-(THREEMONTHAGO)

    Does not work for me

    :(
     
    #8 Haymitch, Jun 13, 2013
  9. Jet Blast

    Jet Blast Member

    Joined:
    Mar 15, 2001
    Messages:
    2,185
    Likes Received:
    178
    In cell A3

    =EDATE(B3,-3)
     
    #9 Jet Blast, Jun 13, 2013
    1 person likes this.
  10. Haymitch

    Haymitch Custom Title

    Joined:
    Dec 22, 2005
    Messages:
    28,371
    Likes Received:
    24,021
    #10 Haymitch, Jun 13, 2013
  11. SwoLy-D

    SwoLy-D Member

    Joined:
    Jul 20, 2001
    Messages:
    37,618
    Likes Received:
    1,456
    So what if it is May 30th? Would it show as February 30th? :cool: Try it.
     
    #11 SwoLy-D, Jun 13, 2013
  12. Haymitch

    Haymitch Custom Title

    Joined:
    Dec 22, 2005
    Messages:
    28,371
    Likes Received:
    24,021
    I tried it. EDATE still works.

    I'm the f***in' Iksell expert now brah.
     
    #12 Haymitch, Jun 13, 2013
  13. Pete the Cheat

    Pete the Cheat Member

    Joined:
    Jun 30, 2006
    Messages:
    3,100
    Likes Received:
    487
    They should be training on this ish instead of algebra in grade school. Still requires a good amount of logic if you create a legit formula string
     
    #13 Pete the Cheat, Jun 13, 2013
  14. Harrisment

    Harrisment Member

    Joined:
    Jun 20, 2001
    Messages:
    15,392
    Likes Received:
    2,158
    #14 Harrisment, Jun 13, 2013
  15. Dr of Dunk

    Dr of Dunk Clutch Crew

    Joined:
    Aug 27, 1999
    Messages:
    46,634
    Likes Received:
    33,637
    Training? Buy a book. Start learning. Heck, find one of the many of Internet sites geared toward Excel and start doing. Basic algebra is useful for some formula building in Excel, like parenthetical expressions. Or need I remind you of this thread : http://bbs.clutchfans.net/showthread.php?t=203263 :grin:
     
    #15 Dr of Dunk, Jun 13, 2013
(You must log in or sign up to post here.)

Share This Page