# Excel: Which products did customers \*not\* buy? **URL:** \ **Category:** Factual Questions\ **Created:** [March 27, 2025, 10:09pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219 "2025-03-27T22:09:14Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![Ruken](https://avatars.discourse-cdn.com/v4/letter/r/f475e1/32.png) [@Ruken](https://boards.straightdope.com/u/Ruken)\ **Post date:** [March 27, 2025, 10:09pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/1 "2025-03-27T22:09:14Z")
Let’s pretend I sell treatments for various conditions. I have lots of data on what customers purchased in the past year. I sell multiple products to help treat each condition. And the more products you use, the better your results. But some customers will only purchase one or two of my products for a given disease. I’d like to quickly identify customers who did not purchase the full suite so I can market the rest of the relevant products to them. I don’t want to waste effort marketing products for conditions they don’t need to treat. Here are some fake data on what people purchased: | Customer | Product | | --- | --- | | Joe | p1 | | Joe | p2 | | Joe | p3 | | Joe | p6 | | Bob | p4 | | Bob | p5 | | Bob | p7 | And here are the products used to treat various conditions: | Condition | Product | | --- | --- | | malingering | p1 | | malingering | p2 | | malingering | p3 | | malingering | p4 | | itchy teeth | p5 | | itchy teeth | p6 | | halitosis | p7 | | halitosis | p8 | I’d like some sort of output that shows me that Joe skipped p4 and p5, and that Bob skipped p1, p2, p3, p6, and p8. I _don’t_ care that Joe sought no product for halitosis, because those data would overwhelm everything else. I feel like solutions might either involve an array FILTER if I can figure out how to generate the correct arrays, or a merge in power query that gives me useful nulls. But I’m stumped. Any ideas? ---
**Author:** ![FinsToTheLeft](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/finstotheleft/32/2930_2.png) [@FinsToTheLeft](https://boards.straightdope.com/u/FinsToTheLeft)\ **Post date:** [March 27, 2025, 10:20pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/2 "2025-03-27T22:20:46Z")
Have you tried selecting the range and adding a pivot table? ---
**Author:** ![Stranger\_On\_A\_Train](https://avatars.discourse-cdn.com/v4/letter/s/13edae/32.png) [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)\ **Post date:** [March 27, 2025, 10:25pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/3 "2025-03-27T22:25:55Z")
Yeah, PivotTables are the answer to doing this quickly and effectively. They may seem arcane when you first try to use this functionality but they really simplify data manipulation and summary statistics versus trying to do all of this manually with filters and formulas. Stranger ---
**Author:** ![aceplace57](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/aceplace57/32/3500_2.png) [@aceplace57](https://boards.straightdope.com/u/aceplace57)\ **Post date:** [March 27, 2025, 10:44pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/4 "2025-03-27T22:44:26Z")
Planning long term. it may be worth importing into MySQL. You’ll have more options to create reports. You can even use a report tool like Crystal Reports to run against the database. Once setup, you can quickly create reports in Crystal. You start by selecting and linking the tables. You can group items, create subtotals by group. It’s data selection is very versatile. I used it nearly every day at work for several years. It depends on your reporting needs. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [March 27, 2025, 10:48pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/5 "2025-03-27T22:48:09Z")
> [@FinsToTheLeft](#): > > adding a pivot table That’s the drum! ---
**Author:** ![FinsToTheLeft](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/finstotheleft/32/2930_2.png) [@FinsToTheLeft](https://boards.straightdope.com/u/FinsToTheLeft)\ **Post date:** [March 27, 2025, 10:54pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/6 "2025-03-27T22:54:28Z")
> [@aceplace57](#): > > Planning long term. it may be worth importing into MySQL. > > You’ll have more options to create reports. You can even use a report tool like Crystal Reports to run against the database. Crystal Reports is obsolete. Power Query is built into Excel and can do all of this, or use Power BI Desktop which is also free. ---
**Author:** ![Ruken](https://avatars.discourse-cdn.com/v4/letter/r/f475e1/32.png) [@Ruken](https://boards.straightdope.com/u/Ruken)\ **Post date:** [March 27, 2025, 11:02pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/7 "2025-03-27T23:02:20Z")
> [@FinsToTheLeft](#): > > Have you tried selecting the range and adding a pivot table? > [@Stranger\_On\_A\_Train](#): > > PivotTables are the answer to doing this quickly and effectively Anything in particular you have in mind? I pivot daily but I’m not seeing anything obvious here. It’s easy to get this: | Row Labels | | | | --- | --- | --- | | Joe | | | | | itchy teeth | | | | | p6 | | | malingering | | | | | p1 | | | | p2 | | | | p3 | | Bob | | | | | halitosis | | | | | p7 | | | itchy teeth | | | | | p5 | | | malingering | | | | | p4 | | Grand Total | | | Which is what they bought. But I don’t know how to show something more along the lines of: | Row Labels | | | | --- | --- | --- | | Joe | | | | | itchy teeth | | | | | p5 | | | malingering | | | | | p4 | | | | | | | | | | Bob | | | | | halitosis | | | | | p8 | | | itchy teeth | | | | | p6 | | | malingering | | | | | p1 | | | | p2 | | | | p3 | | Grand Total | | | Which is what they _didn’t_ buy for conditions they were treating. ---
**Author:** ![Stranger\_On\_A\_Train](https://avatars.discourse-cdn.com/v4/letter/s/13edae/32.png) [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)\ **Post date:** [March 27, 2025, 11:22pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/8 "2025-03-27T23:22:58Z")
You should be able to do a count by product and organize by customer (or vice versa, since you are interested in which customers bought what products) and then you can distinguish between the customers who just bought one or two products, and those who bought most or all. I don’t have Excel in front of me for reference and I mostly do data analysis like this in [Pandas](https://pandas.pydata.org/) these days (which has its own [pivot table functionality](https://pandas.pydata.org//docs/reference/api/pandas.pivot_table.html)) unless I’m building a spreadsheet for someone else to use but you should be able to generate a summary table of what person purchased which products and of how many using Excel’s PivotTable function. You may have to use [Add Measure](https://techcommunity.microsoft.com/discussions/excelgeneral/is-it-possible-to-do-conditional-statements-in-pivot-tables/1514741) or create another column in your spreadsheet that is a joint conditional to link the product and condition being treated; there are multiple ways to skin that cat and unfortunately Excel doesn’t have anything like Python’s lambda function so it’ll be a little clunky but you should be able to work it out. Stranger ---
**Author:** ![Dr.Strangelove](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dr.strangelove/32/6613_2.png) [@Dr.Strangelove](https://boards.straightdope.com/u/Dr.Strangelove)\ **Post date:** [March 27, 2025, 11:43pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/9 "2025-03-27T23:43:22Z")
I tested this out in Google Sheets rather than Excel, but I think they should be equivalent here. Say you have two sheets already, CustomerMap and ProductMap (data starts at row 2). Make a third sheet, CustomerToConditions, with A2 containing: `=UNIQUE(CustomerMap!A2:A)` And B2 containing: `=TRANSPOSE(UNIQUE(FILTER(ProductMap!A$2:A, MATCH(ProductMap!B$2:B, FILTER(CustomerMap!B$2:B, CustomerMap!A$2:A=A2), 0))))` Extend B2 down to all rows. That gets you all conditions for each customer stretched horizontally. Make a fourth sheet, CustomerToProducts. A2 contains: `=UNIQUE(CustomerToConditions!A2:A)` And B2 contains: `=TRANSPOSE(FILTER(ProductMap!B$2:B, ISNUMBER(MATCH(ProductMap!A$2:A, CustomerToConditions!B2:2, 0))))` Again extend B2 down to all rows. This contains all products suitable for treating that customer’s set of conditions. Finally, a sheet with CustomerToProductsFiltered. A2 contains: `=UNIQUE(CustomerToConditions!A2:A)` And B2 onward has: `=FILTER(CustomerToProducts!B2:2, ISNA(MATCH(CustomerToProducts!B2:2, FILTER(CustomerMap!B2:B, CustomerMap!A2:A=A2), 0)))` This correctly shows that Joe should be suggested p4 and p5, while Bob needs p1, p2, p3, p6, and p8. ---
**Author:** ![Stranger\_On\_A\_Train](https://avatars.discourse-cdn.com/v4/letter/s/13edae/32.png) [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)\ **Post date:** [March 28, 2025, 12:08am UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/10 "2025-03-28T00:08:14Z")
> [@Dr.Strangelove](#): > > This correctly shows that Joe should be suggested p4 and p5, while Bob needs p1, p2, p3, p6, and p8. I think that shows that using Excel (or Google Sheets) this way requires a lot of complicated articulations to do basic data analysis, and also makes my head ache. But if I understand what the o.p. is trying todo, I’m pretty sure it can be done just using a PivotTable without all of this business of adding a bunch of extra worksheets. I know I could do this with less than ten lines of Python code including calling libraries and importing data but I know how much people hate hearing how easy it is to do some data mangling task in Python when they’re just trying to make Excel work the way it seems like it should. Stranger ---
**Author:** ![Dr.Strangelove](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dr.strangelove/32/6613_2.png) [@Dr.Strangelove](https://boards.straightdope.com/u/Dr.Strangelove)\ **Post date:** [March 28, 2025, 12:13am UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/11 "2025-03-28T00:13:23Z")
I mean, I’d use SQL instead, but yeah, a baseline spreadsheet program is not that ideal. I don’t know enough about PivotTables to say if there’s a solution there. ETA: The solution above could undoubtedly be simplified by combining the intermediate steps, but for debugging purposes it’s useful to see the results, and there’s not much harm in just having extra sheets. ---
**Author:** ![Stranger\_On\_A\_Train](https://avatars.discourse-cdn.com/v4/letter/s/13edae/32.png) [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)\ **Post date:** [March 28, 2025, 12:18am UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/12 "2025-03-28T00:18:08Z")
> [@Dr.Strangelove](#): > > I mean, I’d use SQL instead, but yeah, a baseline spreadsheet program is not that ideal. But to use SQL you’d first have to put this data in a database. Which, if you are doing this repeatedly and want to keep track of how it evolves over time, is the right solution but it comes with a lot of overhead if you just want to do a quick one-off assessment. Stranger ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [March 28, 2025, 12:23am UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/13 "2025-03-28T00:23:45Z")
Am I correct in the observation that you only know if a patient has a condition if they have purchased the medication? And that while they may have bought different treatments for the same condition, any treatment is used only for one condition? In that case, in a pivot I’d use patients as rows, medications as columns and either count or sum purchases as the value ---
**Author:** ![Stranger\_On\_A\_Train](https://avatars.discourse-cdn.com/v4/letter/s/13edae/32.png) [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)\ **Post date:** [March 28, 2025, 12:30am UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/14 "2025-03-28T00:30:25Z")
> [@penultima\_thule](#): > > In that case, in a pivot I’d use patients as rows, medications as columns and either count or sum purchases as the value I think the o.p. is starting with records of individual sales and trying to summarize them into the kind of table you suggest building, which is what Excel’s PivotTable functionality (and pivot tables in general) do automagically. Of course, anything that can be done with a PivotTable can also be done through a set of cell formulae, macros, or processing data from one sheet into another as @Dr.Strangelove did above, but the point of a PivotTable is to make it easy to do and foolproof in just selecting the data and doing a few configurations. Stranger ---
**Author:** ![Ruken](https://avatars.discourse-cdn.com/v4/letter/r/f475e1/32.png) [@Ruken](https://boards.straightdope.com/u/Ruken)\ **Post date:** [March 28, 2025, 10:00am UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/15 "2025-03-28T10:00:45Z")
> [@Stranger\_On\_A\_Train](#): > > I know I could do this with less than ten lines of Python code including calling libraries and importing data While I cannot currently use python and the real data on the same system, I may use python for the mock data here. > [@penultima\_thule](#): > > Am I correct in the observation that you only know if a patient has a condition if they have purchased the medication? Correct. > [@](#): > > And that while they may have bought different treatments for the same condition, any treatment is used only for one condition? For this contrived example, yes. For the actual scenario, which has nothing to do with treatments or sales, no. But I figured I’d start simple. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [March 28, 2025, 8:01pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/16 "2025-03-28T20:01:24Z")
So this is a version of “if you liked products 1, 2, _or_ 3, you may also like products A, B, _and_ C (that you haven’t yet bought”? Again recognizing the contrived nature of your products & sales example. Looking at it slightly differently, each “product” is a member of a set of related products. And one product may be a member of multiple such sets, but that does not make the sets themselves transitive. ---
**Author:** ![Chronos](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/chronos/32/134_2.png) [@Chronos](https://boards.straightdope.com/u/Chronos)\ **Post date:** [March 29, 2025, 1:01pm UTC](https://boards.straightdope.com/t/excel-which-products-did-customers-not-buy/1016219/17 "2025-03-29T13:01:54Z")
> [@Ruken](#): > > For this contrived example, yes. For the actual scenario, which has nothing to do with treatments or sales, no. But I figured I’d start simple. I think that’s a pretty big jump in complexity, though. If I understand correctly, in the actual problem, if you’ve got a “customer” associated with one “product”, then that might be a part of any of multiple category of “products”, and you can’t know which category they’re actually in. And even if they buy two, and there’s only one category that includes both of those products, they might still not be in that category, but just two different categories.