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

2. ## 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?

3. ## 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

4. ## 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. ## 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.

6. ## Re: Compare two Columns to find missing Items

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

7. ## 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!

#### Posting Permissions

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