Results 1 to 2 of 2

Thread: Finding Duplicates Based on Multiple Criteria

  1. #1

    Finding Duplicates Based on Multiple Criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    A B C D E F G
    1 CASE_NUMBER F_NAME L_NAME B_DATE SEX
    2 10F-22012347 Dixie Abagail 07-Apr-64 F 11F-11012344
    3 11F-11012344 Dixie Abagail 07-Apr-64 F
    4 11F-11012364 Veronica Adams 01-Mar-65 F
    5 11F-11012349 Andy Adams 04-Apr-52 M 11F-11012350
    6 11F-11012350 Andy Adams, Jr. 04-Apr-52 M
    7 11F-11012351 Terri Adams 01-Feb-49 F
    8 11F-11012352 Michael Adams 05-Jun-82 M
    9 11F-11012345 John After 03-Jun-86 M
    10 11F-11012346 Mary Ann Anah 07-Apr-85 F 11F-11012347 11F-11012348
    11 11F-11012347 Mary Anah 07-Apr-85 F
    12 11F-11012348 Mary Ann Anah 07-Apr-85 F


    Here's what I'm looking for:

    In F2: If the first 4 character in B2 are the first 4 characters in a cell in B:B AND the first 4 character in C2 are the first 4 characters in C:C AND D2 is in D: D AND E2 is in E:E THEN return adjacent cell in A:A.

    There may be more than two results that meet the criteria in G2. So G2 should have the 3rd CASE_NUMBER (if there is one)--the same concept/formula in F2, but returning a result that is not already in G2.

    In other words, all the CASE_NUMBERs of the same person should be combined in one row. The results I'm looking for are in Columns F and G.

    However, the same person’s name might have been entered into the databased differently. For instance, Mary Ann [B11] and Mary [B10 & B12] are all the same person. And Andy Adams and Adams, Jr. is the same person. This is why the formula should just search for the first few characters of a name instead of matching it exactly.

    And because it’s possible for different people in the database to have the same F_NAME and L_NAME, the formula should have the extra precaution of checking to make sure the B_DATE and SEX are also the same.


    Help is much appreciated!

  2. #2
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    plz see the file.
    Attached Files Attached Files

Posting Permissions

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