Compare two Columns to find missing Items

evilacha

New Member
Joined
Jul 11, 2006
Messages
25
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...
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top