Finding unique between two groups

Question

Could anyone please tell me how to write a formula for identifying all CustomerIDs who have different products or a change of product base on the below table?

Outcome: on Column C to show the CustomerIDs 1 and 3 that have different Products

Answer 1

As mentioned by @Foxfire And Burns And Burns you can use Filter and Unique if you have O365. It would look like this:

=UNIQUE(FILTER(A2:A11,COUNTIFS(A2:A11,A2:A11,B2:B11,"<>"&B2:B11)<>0))

If you did not have O365, you could revert to a pull-down formula. If you entered it in D2, say, it would be:

=IFERROR(INDEX(A$2:A$11,MATCH(1,(COUNTIFS(A$2:A$11,A$2:A$11,B$2:B$11,"<>"&B$2:B$11)<>0)*(COUNTIF(D1:D$1,A$2:A$11)=0),0)),"")

entered as an array formula.

Answer 2

My formula in column C is:

=COUNTIFS($A$2:$A$11;A2;$B$2:$B$11;B2)=COUNTIF$A$2:$A$11;A2)

This formula will return True/False if the Customer has bought the same product always or not. You are interested in those customer who bought different products so you want to filter by FALSE option in this case:

If you got E365 you got functions like UNIQUE and FILTER, so you could filter the range using formulas instead of manually.