List duplicates with different criteria

[pencekj]

Column A has a list of names, column B a list of phone numbers, and column C a list of device types (smartphone, tablet, mifi, router...). Some names are listed multiple times because a person can have both a phone and tablet that would be different rows of data. What is the easiest way to automatically have a list of all names that have both at least 1 smartphone and at least 1 mifi. The dataset is dynamic and I don't want to have to manually put this information together, but I'm struggling to build it with a pivot table of function

 

[Z51]

Perhaps try this...

Assuming your data starts in row 2, in D1 put a column header, e.g. "Phone-Mifi", then in D2 put the formula:

=SUMPRODUCT(($A$2:$A$14=A2)*(($C$2:$C$14="smartphone")+($C$2:$C$14="mifi")))

You should then be able to select your table and insert a new Pivot table. In the Pivot Table fields, drag the Employee column to the Rows section, then drag the "Phone-Mifi" column to the Filters section, and filter for the value 2.

Adjust your ranges in the SUMPRODUCT formula to include all of your data, of course, making sure the size of the ranges (e.g. A2:A14 and C2:C14) are the same size.

 

[pencekj]

Thanks for the quick reply, Z51. My one issue with this is that someone will show up if they have 2 smartphones and 0 mifis. I only want to include people that have at least 1 of each. Any thoughts?