Excel How to extract distinct values not in second column

Question

How do I write an (array) formula to extract distinct values that do not occur in a second list?

For example

Source list

 - Apple
 - Apple
 - Banana
 - Carrot
 - Carrot
 - Blueberry

Exclusion list

- Carrot

Expected output

- Apple
- Banana
- Blueberry

If you have Excel 2019 or higher, maybe you could try something like =CONCAT(SI($A$1:$A$5<>$C$1;$A$1:$A$5)) but entered as ArrayFormula, and where A1:A5 would be your source list and C1 would be Exclusion list. That would be only a part, still need to remove duplicates. Have you tried something? — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Mar 12, 2020 at 11:55

halfer · Accepted Answer · 2020-03-26 11:56:16Z

2

You can use this formula described at [C2:C4] to get your distinct value:

=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$7)+COUNTIF($B$2,$A$2:$A$7),0),0),"")

or with excel365 described at [E2:E4]:

=UNIQUE(FILTER(A2:A7,A2:A7<>B2))

edited Mar 26, 2020 at 11:56

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Mar 12, 2020 at 12:56

Dang D. Khanh

1,4618 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maarten H Over a year ago

That did the trick! Thanks. Now to see if my laptop can handle this with +/-20k rows in each list ;)

Dang D. Khanh Over a year ago

Hi, Nice to hear that!

Gary's Student · Accepted Answer · 2020-03-12 12:28:25Z

1

If you can use Excel 365 or the Excel web app, then try:

=SORT(UNIQUE(IF(A1:A5<>"Carrot",A1:A5,"")),,-1)

(this is a spill-down, not a standard array formula)

answered Mar 12, 2020 at 12:28

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

Maarten H Over a year ago

Thanks for the reply, unfortunately my 365 does not seem to have SORT or UNIQUE, so that's a no-go for me.

bosco_yip · Accepted Answer · 2020-03-12 12:49:54Z

0

Formula in D2, copied down :

=IFERROR(INDEX(A$2:A$7,AGGREGATE(15,6,ROW(A$2:A$7)-ROW(A$2)+1/(A$2:A$7<>C$2)/(COUNTIF(D$1:D1,A$2:A$7)=0),1)),"")

edited Mar 12, 2020 at 12:49

answered Mar 12, 2020 at 12:43

bosco_yip

3,8322 gold badges8 silver badges10 bronze badges

1 Comment

Maarten H Over a year ago

Very nice. That seems to work only if the Exclusion list has a single value though. It does not accept a range of values (for example C$2:C$3) as a substitute for C$2.

Collectives™ on Stack Overflow

Excel How to extract distinct values not in second column

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related