# crosschecking for duplicates

1. ## crosschecking for duplicates

Hi,

I am trying to create a function crosschecks 2 column's with another 2 columns and takes out any of the duplicates.. adding them to a third set of columns.. to be a little more precise.. Column A & B is the First and Last names of a group of 2000 people.. Column C & D is the first and last names of a group of 1700 duplicate names from column A & B.. so there are 300 names in A & B that are not in C & D... what I am trying to do is find a way to get those names put into column E & F.. is there a way I can do it with functions? if not.. can someone help me find an easier way of doing it than just going down each list and trying to find the names one by one?

thanks,

ben

2. ## RE: crosschecking for duplicates

Hi,

One way, even though, it may not be very elegant is,

Let's suppose that the names are in A2:A2001, B2:B2001, C2:C1701, D2:D1701;

In E2 enter,
=IF(SUMPRODUCT((\$A2=\$C\$2:\$C\$1701)*(\$B2=\$D\$2:\$D\$1701))=0,A2,"")

Drag the formula to F2; or enter the following formula in F2
=IF(SUMPRODUCT((\$A2=\$C\$2:\$C\$1701)*(\$B2=\$D\$2:\$D\$1701))=0,B2,"")

Now, drag these formulas (E2 and F2) down the rows to E2001 and F2001.
These formulas will populate Columns E and F with names that are unique to
Columns A and B, in their respective rows and put the space character in the
rest of the rows.

Now select columns E and F (i.e., E2:F2001), "Copy" --> "Edit" --> "Paste
Special", check "Values" --> "OK" . Select the range E2:F2001 and sort by
column F (last names). The empty rows (containing the space character) in E
and F would clump up as a block, which you can delete, and move the block
containing names to the top of the sheet.

Regards,
B. R. Ramachandran

"bsoder" wrote:

>
> Hi,
>
> I am trying to create a function crosschecks 2 column's with another 2
> columns and takes out any of the duplicates.. adding them to a third
> set of columns.. to be a little more precise.. Column A & B is the
> First and Last names of a group of 2000 people.. Column C & D is the
> first and last names of a group of 1700 duplicate names from column A &
> B.. so there are 300 names in A & B that are not in C & D... what I am
> trying to do is find a way to get those names put into column E & F..
> is there a way I can do it with functions? if not.. can someone help
> me find an easier way of doing it than just going down each list and
> trying to find the names one by one?
>
> thanks,
>
> ben
>
>
> --
> bsoder
> ------------------------------------------------------------------------
> bsoder's Profile: http://www.excelforum.com/member.php...o&userid=29308
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1