# 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

#1
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 •

#2
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:=ISNA(MATCH(A1,\$B\$1:\$B\$4,0))For B1:B4, use this:=ISNA(MATCH(B1,\$A\$1:\$A\$4,0))These formulae will return TRUE if a match isn't found and the Conditional Formatting will be applied.

Report •

#3
February 23, 2010 at 04:46:30
 Hi,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 listIn column B, cells B1 to B26 the second listIn column C enter this formula in cell C1`=IF(ISNA(VLOOKUP(B1,\$A\$1:\$A\$40,1,FALSE)),ROW(),"")`Drag this down to C26, alongside the second list.In column D enter this formula in cell D1`=IF(ISERROR(SMALL(\$C\$1:\$C\$26,ROW())),"",OFFSET(\$B\$1,SMALL(\$C\$1:\$C\$26,ROW())-1,0))`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 DOnce 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.Regards

Report •

Related Solutions

#4
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 duplicatesFor 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 •

#5
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.=IF(ISNA(VLOOKUP(A1,\$B\$1:\$B\$700,1,0)),A1,"")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 •

#6
February 25, 2010 at 08:30:52
 Hi,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 ARegards

Report •

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

Report •

#8
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 •

#9
March 30, 2010 at 04:54:45
 Hi,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:`=IF(ISERROR(xtndVlookup(\$A\$2:\$A\$11,B2,0,0,1,TRUE)),ROW(),"")`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`=IF(ISERROR(SMALL(\$C\$2:\$C\$27,ROW()-1)),"",OFFSET(\$B\$2,SMALL(\$C\$2:\$C\$27,ROW()-1)-2,0))`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 1 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 RangeThe range of cells containing the data to be searchedSearch ValueThe value to be searched for or the address of the cell containing the value to be searched for.Horizontal IndexLike 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.Regards

Report •