Display Company if Company Sells Two Specific Products

Question

I want to know which companies sell apples and oranges. My data set is as follows

Name   Product

CompA  Apple
CompB  Banana
CompA  Orange
CompB  Apple
CompA  Taco

And I'd like the resulting pivot table (or really any reusable formula) to list something like

Name

CompA

Doesn't really matter as long as I get the list of names. I tried doing a fancy pivot table and adding calculated fields, but they never worked.

Tried things like

=IF(Product="Apple",1,0) // Has Apple
=IF(Product="Orange",1,0) // Has Orange
=IF(AND('Has Apple' >= 1, 'Has Orange' >= 1),Name,"") // Has Apple and Orange

But no luck. The first two formulas never even worked.

My real data set is obviously different and much larger which is why I'm trying to do it through fancy formulas/pivot tables.

Is the list of companies dynamic? or are they always the same? — Ricardo Diaz, Feb 12 '19 at 20:34
And the products you compare to are also dynamic, or always the same two? and what excel version do you have? — Ricardo Diaz, Feb 12 '19 at 20:37
Correct, basically assume a random list of companies/products. Running the mac version which for me is 16.16.* — canpan14, Feb 12 '19 at 20:40
You can turn this into a pivot, add companies as rows, product as filter (or slicer) and filter on "Apples" - the pivot update to show target companies with no formulas — urdearboy, Feb 14 '19 at 01:23

QHarr · Accepted Answer · 2019-02-12T20:52:14.397

1

Convert to Excel table then add another column and insert the following formula in C2

=AND(COUNTIFS([Name],[@Name],[Product],"Apple")>0,COUNTIFS([Name],[@Name],[Product],"Orange")>0)

It should autofill down. Create the pivottable and put the Formula field in the page field area and filter on True

edited Feb 12 '19 at 20:52

answered Feb 12 '19 at 20:33

QHarr

83,427
12
54
101

I don't think this would work. I wouldn't be able to tell if CompA had Apple AND Orange. Just if it had one or the other. – canpan14 Feb 12 '19 at 20:43
Sorry I missed the And – QHarr Feb 12 '19 at 20:45

Display Company if Company Sells Two Specific Products

1 Answers1