# Thread: Finding Duplicates Based on Multiple Criteria

1. ## Finding Duplicates Based on Multiple Criteria

 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. Hi,
plz see the file.

#### Posting Permissions

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