Formula to generate a list of all items in Range A that don't appear in Range B?

[alexcr]

Let's say I have a range of values in C1:C25 and a range of values in D1:D50, and I want to generate a list starting in Cell A1 that includes all values in C1:C25 that don't appear in D1:D50. (Note that I don't want this list to include values in D1:D50 that don't appear in C1:C25.)

Is there a formula that I can enter in A1 that would allow me to generate this list? In other words, I'd ideally like to do this with a single formula and without using a helper column.

 

[Jon von der Heyden]

Hi

Perhaps something like: =INDEX($C$1:$C$25,SMALL(IF(ISNA(MATCH($C$1:$C$25,$D$1:$D$50,0)),ROW($C$1:$C$25)),ROW()))

The final ROW() requires your results to begin cascading from row 1. If you were to start from e.g. row 2 then adjust to:
ROW()-ROW($A$1)

 

[alexcr]

Hi

Perhaps something like: =INDEX($C$1:$C$25,SMALL(IF(ISNA(MATCH($C$1:$C$25,$D$1:$D$50,0)),ROW($C$1:$C$25)),ROW()))

The final ROW() requires your results to begin cascading from row 1. If you were to start from e.g. row 2 then adjust to:
ROW()-ROW($A$1)

Thanks, Jon! What if the ranges are instead D10:D45 and E10:E100, and I want to generate a list starting in Cell B10 that includes all values in D10:D45 that don't appear in E10:E100?

 

[Fazza]

for you information another way is using queries - these don't use formulas or code and are just like database queries.
These need a refresh when data changes, and can be set to be automatic on file open, and also every n minutes. Otherwise refreshing is just like for a pivot table.
Or via the keyboard, ALT-D-R. I don't know about using 365 but in earlier versions you can start the wizard to get to S Query with keystrokes ALT-D-D-N.
These are excellent if you have huge amounts of data.

This Microsoft Support article https://support.microsoft.com/en-us/help/136699/description-of-the-usage-of-joins-in-microsoft-query
describes the ones you're describing as subtract joins, and show the SQL for these sorts of queries.

 

[Aladin Akyurek]

In B10 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($D$10:$D$45,SMALL(IF($D$10:$D$45<>"",IF(ISNA(MATCH($D$10:$D$45,$E$10:$E$100,0)),ROW($D$10:$D$45)-ROW(INDEX($D$10:$D$45,1,1))+1)),ROWS($B$10:$B10))),"")

 

[Fluff]

If you have the dynamic array functions.
=FILTER(D10:D45,ISNA(MATCH(D10:D45,E10:E100,0)))

 

[Peter_SSs]

If you have the dynamic array functions.
=FILTER(D10:D45,ISNA(MATCH(D10:D45,E10:E100,0)))

In case there might be blank cells in D10:D45
=FILTER(D10:D45,ISNA(MATCH(D10:D45,E10:E100,0))*(D10:D45<>""))

.. and if you don't have the FILTER function in your version of 365 then standard-entry, copied down
=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(D$10:D$45)/((D$10:D$45<>"")*ISNA(MATCH(D$10:D$45,E$10:E$100,0))),ROWS(B$10:B10))),"")

 

[alexcr]

Thanks everyone for your help! Much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback.