Make a list with criteria and no duplicates

[Stuepef]

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:

=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!

 

[lrobbo314]

Here is some VBA code that will do what you are looking for. It's outputting your results to columns J. You can change that to suite your needs.

Sub UniqueOnes()
Dim AR()
AR = Range("A2:F" & Range("A" & Rows.Count).End(xlUp).Row).Value


With CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(AR)
        If AR(i, 5) = 1 And Not .exists(AR(i, 2)) Then .Add AR(i, 2), i
    Next i
    Range("J1").Resize(UBound(.keys) + 1, 1).Value = Application.Transpose(.keys)
End With


End Sub

 

[Special-K99]

Thats weird, this works, didnt think it would

=IFERROR(INDEX($B$2:$B$20,SMALL(IF(($E$2:$E$20=1),ROW($A$2:$A$20)),ROW(A1))-(ROW(A$2)-1),1),"")
Array formula, use Ctrl-Shift-Enter


Amy doesnt appear on your list using your formula

This also works

=IFERROR(INDEX(A$2:A$20,AGGREGATE(15,6,ROW(A$2:A$20)/(E$2:E$20=1),ROWS(A$2:A2))-(2-1),1),"")

 

[Stuepef]

I have tried both of your formulas and they create duplicates like this:

Bobby Jackson
Bill O'Brian
Matt James
Bobby Jackson
George Wilks
Matt James
Amy Belts

I tried a few different formulas, do you see any issue with this:

{=IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($L$2:L2,$A$2:$A$20),0)),"")}

 

[Special-K99]

I have tried both of your formulas and they create duplicates like this:

Bobby Jackson
Bill O'Brian
Matt James
Bobby Jackson
George Wilks
Matt James
Amy Belts

I tried a few different formulas, do you see any issue with this:

{=IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($L$2:L2,$A$2:$A$20),0)),"")}

Yep, my formulas were rubbish, I see the duplicates now.
Still thinking..

 

[AhoyNC]

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag down as needed.

Excel Workbook
	A	B	C	D	E	F
1	Family Group	Employee Name	# of People	DOB	Relationship	Package
2	Bobby Jackson	Bobby Jackson	4	1/4/1975	1	MEDICAL
3	Bobby Jackson	Sandra Jackson	4	8/23/1973	2	MEDICAL
4	Bobby Jackson	Collette Jackson	4	5/12/2004	3	MEDICAL
5	Bobby Jackson	Joe Jackson	4	6/7/2008	3	MEDICAL
6	Bill O'Brian	Bill O'Brian	2	4/12/1969	1	MEDICAL
7	Bill O'Brian	Jackie O'Brian	2	9/4/1971	2	MEDICAL
8	Matt James	Matt James	3	7/10/1987	1	MEDICAL
9	Matt James	Beckie James	3	4/11/1990	2	MEDICAL
10	Matt James	Richard James	3	12/5/2010	3	MEDICAL
11	Bobby Jackson	Bobby Jackson	4	1/4/1975	1	DENTAL
12	Bobby Jackson	Sandra Jackson	4	8/23/1973	2	DENTAL
13	Bobby Jackson	Collette Jackson	4	5/12/2004	3	DENTAL
14	Bobby Jackson	Joe Jackson	4	6/7/2008	3	DENTAL
15	George Wilks	George Wilks	2	4/12/1969	1	DENTAL
16	George Wilks	Jackie Wilks	2	9/4/1971	2	DENTAL
17	Matt James	Matt James	3	7/10/1987	1	DENTAL
18	Matt James	Beckie James	3	4/11/1990	2	DENTAL
19	Matt James	Richard James	3	12/5/2010	3	DENTAL
20	Amy Belts	Amy Belts	1	12/30/1954	1	DENTAL
21
22	Employee Name
23	Bobby Jackson
24	Bill O'Brian
25	Matt James
26	George Wilks
27	Amy Belts
Sheet

 

[Stuepef]

Thank you for the replies!