Excel 比较列表 2(当前)与列表 1(现有)的重复项和新项

Question

Existing    Current    Duplicates   New
------------------------------------------
apple       banana     banana       plum
orange      plum       grapes       mango
banana      grapes
strawberry  mango    
grapes 

   

根据“当前”列表获取“重复”和"new"的任何建议？

Answer 1

Office 365 解决方案:=FILTER(B2:B5,COUNTIF(A2:A6,B2:B5))=FILTER(B2:B5,COUNTIF(A2:A6,B2:B5)=0)旧版 Excel:=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$5)/COUNTIF($A$2:$A$6,$B$2:$B$5),ROW(1:1))),"")在 C2抄下来。=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$5)/(COUNTIF($A$2:$A$6,$B$2:$B$5)=0),ROW(1:1))),"")在 D2抄下来。