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))),"")

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Family Group[/TD]
[TD]Employee Name[/TD]
[TD]# of People[/TD]
[TD]DOB[/TD]
[TD]Relationship[/TD]
[TD]Package[/TD]
[/TR]
[TR]
[TD]Bobby Jackson[/TD]
[TD]Bobby Jackson[/TD]
[TD]4[/TD]
[TD]01/04/1975[/TD]
[TD]1[/TD]
[TD]MEDICAL[/TD]
[/TR]
[TR]
[TD]Bobby Jackson[/TD]
[TD]Sandra Jackson[/TD]
[TD]4[/TD]
[TD]8/23/1973[/TD]
[TD]2[/TD]
[TD]MEDICAL[/TD]
[/TR]
[TR]
[TD]Bobby Jackson[/TD]
[TD]Collette Jackson[/TD]
[TD]4[/TD]
[TD]5/12/2004[/TD]
[TD]3[/TD]
[TD]MEDICAL[/TD]
[/TR]
[TR]
[TD]Bobby Jackson[/TD]
[TD]Joe Jackson[/TD]
[TD]4[/TD]
[TD]6/7/2008[/TD]
[TD]3[/TD]
[TD]MEDICAL[/TD]
[/TR]
[TR]
[TD]Bill O'Brian[/TD]
[TD]Bill O'Brian[/TD]
[TD]2[/TD]
[TD]4/12/1969[/TD]
[TD]1[/TD]
[TD]MEDICAL[/TD]
[/TR]
[TR]
[TD]Bill O'Brian[/TD]
[TD]Jackie O'Brian[/TD]
[TD]2[/TD]
[TD]9/4/1971[/TD]
[TD]2[/TD]
[TD]MEDICAL[/TD]
[/TR]
[TR]
[TD]Matt James[/TD]
[TD]Matt James[/TD]
[TD]3[/TD]
[TD]7/10/1987[/TD]
[TD]1[/TD]
[TD]MEDICAL[/TD]
[/TR]
[TR]
[TD]Matt James[/TD]
[TD]Beckie James[/TD]
[TD]3[/TD]
[TD]4/11/1990[/TD]
[TD]2[/TD]
[TD]MEDICAL[/TD]
[/TR]
[TR]
[TD]Matt James[/TD]
[TD]Richard James[/TD]
[TD]3[/TD]
[TD]12/5/2010[/TD]
[TD]3[/TD]
[TD]MEDICAL[/TD]
[/TR]
[TR]
[TD]Bobby Jackson[/TD]
[TD]Bobby Jackson[/TD]
[TD]4[/TD]
[TD]1/4/1975[/TD]
[TD]1[/TD]
[TD]DENTAL[/TD]
[/TR]
[TR]
[TD]Bobby Jackson[/TD]
[TD]Sandra Jackson[/TD]
[TD]4[/TD]
[TD]8/23/1973[/TD]
[TD]2[/TD]
[TD]DENTAL[/TD]
[/TR]
[TR]
[TD]Bobby Jackson[/TD]
[TD]Collette Jackson[/TD]
[TD]4[/TD]
[TD]5/12/2004[/TD]
[TD]3[/TD]
[TD]DENTAL[/TD]
[/TR]
[TR]
[TD]Bobby Jackson[/TD]
[TD]Joe Jackson[/TD]
[TD]4[/TD]
[TD]6/7/2008[/TD]
[TD]3[/TD]
[TD]DENTAL[/TD]
[/TR]
[TR]
[TD]George Wilks[/TD]
[TD]George Wilks[/TD]
[TD]2[/TD]
[TD]4/12/1969[/TD]
[TD]1[/TD]
[TD]DENTAL[/TD]
[/TR]
[TR]
[TD]George Wilks[/TD]
[TD]Jackie Wilks[/TD]
[TD]2[/TD]
[TD]9/4/1971[/TD]
[TD]2[/TD]
[TD]DENTAL[/TD]
[/TR]
[TR]
[TD]Matt James[/TD]
[TD]Matt James[/TD]
[TD]3[/TD]
[TD]7/10/1987[/TD]
[TD]1[/TD]
[TD]DENTAL[/TD]
[/TR]
[TR]
[TD]Matt James[/TD]
[TD]Beckie James[/TD]
[TD]3[/TD]
[TD]4/11/1990[/TD]
[TD]2[/TD]
[TD]DENTAL[/TD]
[/TR]
[TR]
[TD]Matt James[/TD]
[TD]Richard James[/TD]
[TD]3[/TD]
[TD]12/5/2010[/TD]
[TD]3[/TD]
[TD]DENTAL[/TD]
[/TR]
[TR]
[TD]Amy Belts[/TD]
[TD]Amy Belts[/TD]
[TD]1[/TD]
[TD]12/30/1954[/TD]
[TD]1[/TD]
[TD]DENTAL[/TD]
[/TR]
</tbody>[/TABLE]


Here is what I am attempting to do with the formula:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[/TR]
[TR]
[TD]Bobby Jackson[/TD]
[/TR]
[TR]
[TD]Bill O'Brian[/TD]
[/TR]
[TR]
[TD]Matt James[/TD]
[/TR]
[TR]
[TD]George Wilks[/TD]
[/TR]
[TR]
[TD]Amy Belts[/TD]
[/TR]
</tbody>[/TABLE]

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!