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

What do you want to happen when the non-duplicates are "found". Do you want them listed in a separate column, or highlighted, or...?

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.

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 list

In 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 notappear 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

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

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.

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

Wow thanks for the guidance. I applaud your righteousness, you've been tramendous help!

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?

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 FaucetThe arguments for extended VLOOKUP() are:

Search Range

The range of cells containing the data to be searchedSearch 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 benegativeto 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

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History