Excel 07 Macro to find Non Duplicates

February 22, 2010 at 13:36:54
Specs: Windows XP
I want to compare two columns on Excel 2007 and look for only NON DUPLICATES. I searched around for macros but all i could find were macros to find Duplicates. Please any help would be appreciated. Thank you

See More: Excel 07 Macro to find Non Duplicates

Report •

February 22, 2010 at 13:57:10
What do you want to happen when the non-duplicates are "found".

Do you want them listed in a separate column, or highlighted, or...?

Report •

February 22, 2010 at 19:20:08
Just in case you want the non-duplicates highlighted, try this Conditional Formatting method:

Assuming one list is in A1:A4 and the other is in B1:B4,

Select A1:A4 and use this formula in Conditional Formatting:


For B1:B4, use this:


These formulae will return TRUE if a match isn't found and the Conditional Formatting will be applied.

Report •

February 23, 2010 at 04:46:30

Here is a way of listing the unmatched items using formulas.

For this example I used the following lists:
In column A, cells A1 to A40 the first list
In column B, cells B1 to B26 the second list

In column C enter this formula in cell C1


Drag this down to C26, alongside the second list.

In column D enter this formula in cell D1


Drag this down to D26, alongside the second list.

The list of values in column B that do not appear in column A are shown at the top of column D

Once you have this working you can drag the formulas in column C out of view (say to column AA) or hide the the column, so that the intermediate step does not show.


Report •

Related Solutions

February 25, 2010 at 07:01:37
Humar and DerbyDad03, Thank you for your speedy response.

DerbyDad03 If possible, I would like to have the NON Duplicate that are found to appear on a different column.

Humar, when i tried your strategy it actually gave me a listing of duplicates instead of non duplicates

For ex: my column A has 1500 IP address listings, my colomn B has about 700 IP address (but these are all scattered duplicates from column A)... out of the approximate remaining 800, i would like to know which ones are the NON Duplicates

Report •

February 25, 2010 at 08:22:37
So you want to see the 800 IP address from column A that are not in Column B, right?

A quick and dirty way would be to drag this down Column C, then do Edit...PasteSpecial Values...Sort on Column C.


This will put the non-matching IP's from Column A in the same row in Column C. Pasting the values and then sorting would moved them all to the top of Column C.

I can work on a "cleaner" method after my next meeting.

Report •

February 25, 2010 at 08:30:52

Here is a small sample using the formulas I suggested, modified for the smaller ranges.

This may help in identifying what is compared to what!

	A		B	C	D
1	Card		Gold		Silver
2	Element		Silver	2	July
3	Agri		Juno		
4	Bill		July	4	
5	Stickers	Card		
6	Gold			
7	Juno			
8	Cave			
9	Mill			
10	Faucet			

Column D has the Non-duplicates, i.e. any item in column B that is not present in column A


Report •

February 25, 2010 at 13:11:07
Wow thanks for the guidance. I applaud your righteousness, you've been tramendous help!

Report •

March 29, 2010 at 05:23:10
This is awesome! However, I'm noticing it does not pick up case sensitive text - is there a way to turn this around to pick up case sensitive text?

Report •

March 30, 2010 at 04:54:45

Unfortunately Excel's VLOOKUP() function is not case sensitive.

There is an alternative, extended function called XTNDVLOOKUP() here.
If you follow the instructions and load this function, you can use it to get a case sensitive version.

Using the example I gave, use this in cell C2:


Note that the data must not start on row 1 - this is due to a limitation of the extended vlookup.
Drag the formula down alongside all the cells containing data to be matched in column B.
The formula in cell D2 is

Again, drag this down as many rows as there is data in column B.

This is what the example looks like (case sensitive):

	A		B	C	D
2	Card		Gold		Silver
3	Element		Silver	3	juno
4	Agri		juno	4	July
5	Bill		July	5	
6	Stickers	Card		
7	Gold			
8	Juno			
9	Cave			
10	Mill			
11	Faucet			

The arguments for extended VLOOKUP() are:
Search Range
The range of cells containing the data to be searched

Search Value
The value to be searched for or the address of the cell containing the value to be searched for.

Horizontal Index
Like VLOOKUP, the column to return a result from. However the column number can be negative to return a result from a column to the left of the item found. If the Horizontal and Vertical index values are both zero, the function returns the relative position of the matching item in the list, rather than the item itself, which is what Vlookup() does.

Vertical Index. (defaults to 0)
This allows a result to be returned from a row before or a row after the found item. Useful for returning the next item in a list. For example you can return the item following the one you searched for in the list.

Search instance (defaults to 0)
You can search for the second or third or nth instance of a value in a list. If the value is negative you can search from the end of your range. -1 will find the last instance of a value in the search range.

Match (defaults to TRUE)
Match is TRUE for a case sensitive match and FALSE for case insensitive.


Report •

Ask Question