Excel Comparing List 2(current) against List 1(Existing) for duplicates and new items-CodePudding

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

Any suggestion to get the "duplicate" and "new" based on the "current" list?

CodePudding user response：

Office 365 solution:

=FILTER(B2:B5,COUNTIF(A2:A6,B2:B5)) =FILTER(B2:B5,COUNTIF(A2:A6,B2:B5)=0)

Older 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))),"") in C2 copied down. =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))),"") in D2 copied down.

CodePudding user response：

I admit that my solution is not working, but you might be helped with the general idea:

I started with two formulas:

=IF(COUNTIF(A$1:A$6,B2)>0,B2,"")
=IF(COUNTIF(A$1:A$6,B2)=0,B2,"")

As you see, I used COUNTIF() to see how many times an item from column B appears in column A, and based on that I did two things:

- If the item appears at least one time, it is shown, else an empty string is shown.
- If the itema appears not at all, it is shown, else an empty string is shown.

Like that, you get two columns with the values you're looking for.

Then it was my idea to "sort" them, by taking the first entry, then the second, then the third, ..., using the LARGE() function, but that seems not to work (LARGE() only seems to work on numbers). If you would find a way to get the largest, then the second largest, ... from an array of strings, you might find a solution.