Results 1 to 7 of 7

Compare two Columns to find missing Items

This is a discussion on Compare two Columns to find missing Items within the Excel Questions forums, part of the Question Forums category; Hi guys, I have not used Excel in a long time and I forgot how to compare two different columns ...

  1. #1
    New Member
    Join Date
    Jul 2006
    Posts
    25

    Default Compare two Columns to find missing Items

    Hi guys, I have not used Excel in a long time and I forgot how to compare two different columns and highlight or extract the items that are presentin one of the columns but not in the other one.

    Thanks in advance...

  2. #2
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,927

    Default Re: Compare two Columns to find missing Items

    So let me make sure I understand you want to check to make sure we'll say column A has a value in say Column B?

    A - B
    1 - 2
    3 - 7
    56 - 4
    4 - 56

    So would you want 1 and 3 highlighted in Column A and 2 and 7 in B or just column A highlighted. Will both columns have differences? You could use conditional formatting and the match function for this?
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  3. #3
    Board Regular Domski's Avatar
    Join Date
    Jan 2005
    Location
    Leeds, UK
    Posts
    7,178

    Default Re: Compare two Columns to find missing Items

    Hi,

    You could use a MATCH formula to check if items in one were in another:

    =MATCH(A1,$C$1:$C$100,0)

    and vice versa.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    - Guidelines For Posting

    - Mr Excel Articles and PodCasts

    - Display sheet using HTML Maker or Excel Jeanie

    - Something that makes me laugh

    - Please try to remember to use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    New Member
    Join Date
    Jul 2006
    Posts
    25

    Default Re: Compare two Columns to find missing Items

    I did not explain my self well enough, I have two columns A and B. Column A has more than 1000 rows with diferent skus; then I have Column B with less rows therefore some skus are missing... and I want to know which skus from Column A are missing in column B. Any Idea ?

    Thanks

  5. #5
    Board Regular Domski's Avatar
    Join Date
    Jan 2005
    Location
    Leeds, UK
    Posts
    7,178

    Default Re: Compare two Columns to find missing Items

    I wouldn't necessarily advise having the data in adjacent columns. The Match formula can be used to check each item in column A if it exists in column B though. Use it in Column C like:

    =MATCH(A1,$B$1:$B$2000,0)

    and copy it down the lists. Where it finds a match it will return the relative position of the item checked in column A in column B and #N/A for items it can't find.
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    - Guidelines For Posting

    - Mr Excel Articles and PodCasts

    - Display sheet using HTML Maker or Excel Jeanie

    - Something that makes me laugh

    - Please try to remember to use code tags when posting your VBA code: [code] Your code here [/code]

  6. #6
    New Member
    Join Date
    Jul 2006
    Posts
    25

    Default Re: Compare two Columns to find missing Items

    Thanks man, thats what i just did... and works fine.

  7. #7
    New Member
    Join Date
    Jan 2003
    Location
    First Health
    Posts
    41

    Default Re: Compare two Columns to find missing Items

    Hi, all. I'm trying to do the same thing: I have a two lists of numbers in Columns D & E, Rows 2 thru 163. My formula in F2 is =MATCH(D2,$E$2:$E$163,0). I copied this down Column F thru Row 163. It *appears* to work, in that cells in Column F are populated with correct row numbers for most matches, however where it returns #N/A, (which should indicate that the value in the corresponding cell in Column D is not found in Column E, right?) I can see that it is wrong - at least some of those values ARE found within the specified range in Column E. What am I missing or doing wrong??
    Thanks!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com