Customers who have not bought specific item

[sammm120]

Hi,

Can anybody help with the following...

I am looking to extract customer order numbers where the customer has not purchased a specific item.

I have attached the data I have to work with.

For example, customer order 1510032 only purchased a regular item and did not purchase a replica. Customer order 1510054 purchased a regular item and a certificate and did not purchase a replica.

I need to highlight all of the order numbers where the customer did not purchase a replica


Is this possible?

Thanks

[Fluff13]

If you want a list how about

Please Login or Register  to view this content.

[sammm120]

Hi..

I think that has worked, thank you so much.

[Fluff13]

Glad to help & thanks for the feedback.

[sammm120]

Is there a way I can copy the results from the formula to paste them elsewhere? When I highlight the column and copy, it pastes blank

[Fluff13]

Select the first cell with the formula then Ctrl A, Ctrl C & then paste as values wherever you want.

[sammm120]

Hi.

I am going back to do some work on this particular exercise.

If I added an extra column to the data, e.g. Column D and this contained a list of email addresses, is there any way that I could get the data in that field to pull through with the results as well?

Thanks,
Sam

[AliGW]

Provide a sample workbook showing the new layout and expected results.

[sammm120]

Hi Here is the example

[AliGW]

Try this (you do NOT need to enter this as an array formula:

=LET(a,UNIQUE(FILTER(A2:A2000,COUNTIFS(A2:A2000,A2:A2000,C2:C2000,"*replica*")=0)),d,INDEX(D2:D2000,MATCH(a,A2:A2000,0)),CHOOSE({1,2},a,d))

[sammm120]

Thanks so much

[AliGW]

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[sammm120]

It has been a while since this thread, but I am hoping you can help further.

How can I edit the below formula so that I include two extra columns - columns E and F in the result?

=LET(a,UNIQUE(FILTER(A2:A2000,COUNTIFS(A2:A2000,A2:A2000,C2:C2000,"*replica*")=0)),d,INDEX(D2:D2000,MATCH(a,A2:A2000,0)),CHOOSE({1,2},a,d))

Many thanks