Matches where an occurrence of X is Y but the corresponding Z is missing

Question

I am trying to create a formula (or script) that is able to find all the occurrences of a plate number and return 1 if 'BOUGHT' has been found, but 'SOLD' is missing for that plate number.

The dataset that looks like this:

Column A is a list of plate numbers and B is the type ('BOUGHT' or 'SOLD'). I need to find the plate numbers for which there is a 'BOUGHT' but no 'SOLD'.

---

I tried using the formula below (and many others):

=ARRAYFORMULA(IFERROR(IF(
((VLOOKUP(A2:A,A:B,2,0)="VÉTEL")
+
(VLOOKUP(A2:A,A:B,2,0)="ELADÁS"))<>2
,1,0)))

---

Since nothing seems to be working, any help would be highly appreciated.

I created a sample worksheet, which can be found here.

Answer 1

Try below formula-

=ArrayFormula(IF(COUNTIFS(A2:A,A2:A,B2:B,"BOUGHT")>0,IF(COUNTIFS(A2:A,A2:A,B2:B,"BOUGHT")*(COUNTIFS(A2:A,A2:A,B2:B,"SOLD"))=0,1,""),""))

See your sheet.