I have a sheet with 100 000 records It is just a product ID and a Year
I need to extract the IDs that have the year 2019 only - and no earlier year than that. So in the image below I just want to get line 9 and 10. For example, Product ID 4488, has year 2013,2012,2010 so those I want to exclude.
I have tried some conditional formulas but I dont get the result I want
CodePudding user response：
If all entries are unique (same ID with same year not repeated). You could use something like this =IF(AND(COUNTIF(A:A,A2)=1,B2=2019),A2,"") in C2 and doubleclick or drag down.
COUNTIF finds how many times ID appears in a list (obviously more than one time would mean that there are multiple years). So rest of formula checks if ID appeared only one time and is year 2019. Result:
CodePudding user response：
As 
• Formula used in cell D1
=VSTACK(A1:B1,FILTER(A2:B10,COUNTIFS(A2:A10,A2:A10,B2:B10,"<>"&2019)=0))
Or, As the above formula requires, you need to be in MS365, hence here is a formula which works with Excel 2010 onwards
• Formula used in cell G2
=IFERROR(INDEX($A$2:$B$10,AGGREGATE(15,6,(ROW($B$2:$B$10)-1)/
(COUNTIFS($A$2:$A$10,$A$2:$A$10,$B$2:$B$10,"<>"&2019)=0),ROW(A1)),0),"")
Depending on excel version one may need to press CTRL SHIFT ENTER