I receive a spreadsheet with the information listed below. The spreadsheet has a list of employees, spouse, children with DOB's and packages (Medical & Dental). I want to create a formula that extracts every employee with the relationship 1 without duplicates. My current formula extracts employees with the 1 relationship, but duplicates due to some employees having both medical AND dental packages. I am looking for help on re configuring the following formula to list every employee with relationship 1 without duplicates:
<tbody>
</tbody>
Here is what I am attempting to do with the formula:
<tbody>
</tbody>
thank you for your help!
Code:
=IFERROR(INDEX($A$2:$A$20,AGGREGATE(15,6,ROW($2:$20)/($E$2:$E$20=1),ROW(1:1))),"")
Family Group | Employee Name | # of People | DOB | Relationship | Package |
Bobby Jackson | Bobby Jackson | 4 | 01/04/1975 | 1 | MEDICAL |
Bobby Jackson | Sandra Jackson | 4 | 8/23/1973 | 2 | MEDICAL |
Bobby Jackson | Collette Jackson | 4 | 5/12/2004 | 3 | MEDICAL |
Bobby Jackson | Joe Jackson | 4 | 6/7/2008 | 3 | MEDICAL |
Bill O'Brian | Bill O'Brian | 2 | 4/12/1969 | 1 | MEDICAL |
Bill O'Brian | Jackie O'Brian | 2 | 9/4/1971 | 2 | MEDICAL |
Matt James | Matt James | 3 | 7/10/1987 | 1 | MEDICAL |
Matt James | Beckie James | 3 | 4/11/1990 | 2 | MEDICAL |
Matt James | Richard James | 3 | 12/5/2010 | 3 | MEDICAL |
Bobby Jackson | Bobby Jackson | 4 | 1/4/1975 | 1 | DENTAL |
Bobby Jackson | Sandra Jackson | 4 | 8/23/1973 | 2 | DENTAL |
Bobby Jackson | Collette Jackson | 4 | 5/12/2004 | 3 | DENTAL |
Bobby Jackson | Joe Jackson | 4 | 6/7/2008 | 3 | DENTAL |
George Wilks | George Wilks | 2 | 4/12/1969 | 1 | DENTAL |
George Wilks | Jackie Wilks | 2 | 9/4/1971 | 2 | DENTAL |
Matt James | Matt James | 3 | 7/10/1987 | 1 | DENTAL |
Matt James | Beckie James | 3 | 4/11/1990 | 2 | DENTAL |
Matt James | Richard James | 3 | 12/5/2010 | 3 | DENTAL |
Amy Belts | Amy Belts | 1 | 12/30/1954 | 1 | DENTAL |
<tbody>
</tbody>
Here is what I am attempting to do with the formula:
Employee Name |
Bobby Jackson |
Bill O'Brian |
Matt James |
George Wilks |
Amy Belts |
<tbody>
</tbody>
thank you for your help!
Last edited: