Make a list with criteria and no duplicates

Stuepef

Board Regular
Joined
Oct 23, 2017
Messages
110
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:

Code:
=IFERROR(INDEX($A$2:$A$20,AGGREGATE(15,6,ROW($2:$20)/($E$2:$E$20=1),ROW(1:1))),"")
Family GroupEmployee Name# of PeopleDOBRelationshipPackage
Bobby JacksonBobby Jackson401/04/19751MEDICAL
Bobby JacksonSandra Jackson48/23/19732MEDICAL
Bobby JacksonCollette Jackson45/12/20043MEDICAL
Bobby JacksonJoe Jackson46/7/20083MEDICAL
Bill O'BrianBill O'Brian24/12/19691MEDICAL
Bill O'BrianJackie O'Brian29/4/19712MEDICAL
Matt JamesMatt James37/10/19871MEDICAL
Matt JamesBeckie James34/11/19902MEDICAL
Matt JamesRichard James312/5/20103MEDICAL
Bobby JacksonBobby Jackson41/4/19751DENTAL
Bobby JacksonSandra Jackson48/23/19732DENTAL
Bobby JacksonCollette Jackson45/12/20043DENTAL
Bobby JacksonJoe Jackson46/7/20083DENTAL
George WilksGeorge Wilks24/12/19691DENTAL
George WilksJackie Wilks29/4/19712DENTAL
Matt JamesMatt James37/10/19871DENTAL
Matt JamesBeckie James34/11/19902DENTAL
Matt JamesRichard James312/5/20103DENTAL
Amy BeltsAmy Belts112/30/19541DENTAL

<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:

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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.

Code:
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
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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),"")
 
Last edited:

Stuepef

Board Regular
Joined
Oct 23, 2017
Messages
110
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:
Code:
{=IFERROR(INDEX($A$2:$A$20,MATCH(0,COUNTIF($L$2:L2,$A$2:$A$20),0)),"")}
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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:
Code:
{=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

Well-known Member
Joined
Oct 10, 2011
Messages
4,344
Office Version
365
Platform
Windows
Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag down as needed.
Excel Workbook
ABCDEF
1Family GroupEmployee Name# of PeopleDOBRelationshipPackage
2Bobby JacksonBobby Jackson41/4/19751MEDICAL
3Bobby JacksonSandra Jackson48/23/19732MEDICAL
4Bobby JacksonCollette Jackson45/12/20043MEDICAL
5Bobby JacksonJoe Jackson46/7/20083MEDICAL
6Bill O'BrianBill O'Brian24/12/19691MEDICAL
7Bill O'BrianJackie O'Brian29/4/19712MEDICAL
8Matt JamesMatt James37/10/19871MEDICAL
9Matt JamesBeckie James34/11/19902MEDICAL
10Matt JamesRichard James312/5/20103MEDICAL
11Bobby JacksonBobby Jackson41/4/19751DENTAL
12Bobby JacksonSandra Jackson48/23/19732DENTAL
13Bobby JacksonCollette Jackson45/12/20043DENTAL
14Bobby JacksonJoe Jackson46/7/20083DENTAL
15George WilksGeorge Wilks24/12/19691DENTAL
16George WilksJackie Wilks29/4/19712DENTAL
17Matt JamesMatt James37/10/19871DENTAL
18Matt JamesBeckie James34/11/19902DENTAL
19Matt JamesRichard James312/5/20103DENTAL
20Amy BeltsAmy Belts112/30/19541DENTAL
21
22Employee Name
23
24Bill O'Brian
25Matt James
26George Wilks
27Amy Belts
Sheet
 

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top