+ Reply to Thread
Results 1 to 2 of 2

crosschecking for duplicates

  1. #1
    Registered User
    Join Date
    12-02-2005
    Posts
    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. #2
    B. R.Ramachandran
    Guest

    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
    > View this thread: http://www.excelforum.com/showthread...hreadid=490282
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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