Did customer in one list buy an item in another list?

[megera716]

Customer produces and sells widgets (CW).

We sell a report about the performance of the widgets.

I have a spreadsheet of customers with one report sale per row - Customer Name in Column A, Product Name in Column B, other data (irrelevant to my question) in other columns. Customer may have purchased none, one, multiple or all reports.

I have a second spreadsheet of the reports with one CW per row and each report is about multiple CWs so there may be 10-40 CW lines for each report, with the report name in Column A and the name of the CW in Column B and the name of the customer in Column C.

I want to know did the customer -- who produces a CW -- purchase the report in which we talk about said CW. I'm thinking a format like this with either a count or the sum of the dollars or something in the table values.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]CW in Report, Purchased Report[/TD]
[TD]CW Not in Report, Purchased Report[/TD]
[TD]CW In Report, Didn't Purchae[/TD]
[/TR]
[TR]
[TD]Report1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] CustomerA[/TD]
[TD] xxx[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] CustomerB[/TD]
[TD][/TD]
[TD]xxx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] CustomerC[/TD]
[TD]xxx[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] CustomerC[/TD]
[TD][/TD]
[TD][/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD] CustomerM[/TD]
[TD]xxx[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] CustomerX[/TD]
[TD][/TD]
[TD]xxx[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Hi, to be more specific.
You could put 3 sheets.
One with the data from the customer sheet.
Two with the data from the second sheet.
And Three, with the expected result, explaining a couple of examples, of course, using the data shown in sheets 1 and 2.

 

[megera716]

Yes, I am trying to consolidate all the data using a pivot or index/match or vlookup so it can be easily viewed in one place like the table I provided. However, I'm not sure HOW to do that.

 

[DanteAmor]

Yes, I am trying to consolidate all the data using a pivot or index/match or vlookup so it can be easily viewed in one place like the table I provided. However, I'm not sure HOW to do that.

I help you, but I need to know how your data is and the result you expect.


Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[megera716]

I help you, but I need to know how your data is and the result you expect.


Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

Thank you for the clarification . I did think that what I provided in my OP was sufficient but I understand now that it isn't. Because the specifics would be very identifying, I will come back to this after the weekend when I have had time to anonymize some of the data. Thanks!

 

[DanteAmor]

Thank you for the clarification . I did think that what I provided in my OP was sufficient but I understand now that it isn't. Because the specifics would be very identifying, I will come back to this after the weekend when I have had time to anonymize some of the data. Thanks!

In agreement