INDEX MATCH with multiple criteria Part 2

[Stuepef]

I want to create a list of all employees that have a 1 in column E. Note that employees can be listed multiple times based on having both MEDICAL & DENTAL packages.

Here is a link to the original post I had to find all employees with a 1 relationship and medical package:
https://www.excelforum.com/excel-for...ml#post4940860

I am looking to update the formula to include all employees with a relationship 1 regardless of package without duplicates. I have the desired results within the attached worksheet.

I appreciate your help!

[dflak]

OK, this was fun. I solved it with the help of a number of helper columns.

First of all I converted the data to an Excel Table because: (A) Excel tables know how big they are and any formulas, charts and pivot tables built from them always reference the exact amount of data they have to and (B) you can use the column headers in the formulas which makes it easier to understand them

The first helper column was to make a composite of the Group and Name - this is what makes a record unique. =[@Group]&":"&[@Name].

Then I got an indication if the record was unique: =MATCH([@Composite],[Composite],0)=ROW()-1 - this formula is only true if for the first occurrence of the combination.

Then I got the type of family member =VLOOKUP([@Relationship],Table_Relation,2,FALSE) - this resolved to Self, Spouse and Child.

Then I had to figure out the order of the children: =COUNTIFS([Group],[@Group],[New Rel],"Child",[DOB],"<=" &[@DOB]+ROW()/24,[Unique],TRUE) - this got the number of children in the Group who are older than the child in question. I threw in +ROW()/24 as a tie breaker on DOB (twins). Twins, triplets, etc., who share the same DOB are assumed to be born an hour apart.

Finally, I could construct the member's "standing" =IF([@[New Rel]]="Child","Child "&[@[Child Number]],[@[New Rel]])

Now with all this information, I could construct a pivot table. A family can have any number of children under this scheme. About the only thing I would do is drag and drop Self and Spouse to the 1 and 2 column positions in the pivot table.