nikzter
New member
- Joined
- Apr 5, 2015
- Messages
- 1
- Reaction score
- 0
- Points
- 0
| 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.
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!