Find items in one list but not in another

[JHL]

Hello, using Excel 2000...
I found the formula I need on CPearson.com, but maybe due to the size of
my spreadsheet all that happens is the hour glasse turns and turns.
When I go task manager, it says Excel is not responding.

List1 has 14409 lines and List2 has 9217 lines. Both are named List1
and List2, with List2 the same size as List1. A 3rd column "In 1 not 2"
is named and sized to List1.

The formula I found =IF(ISERROR(MATCH(List1,List2,0)),List1,"")

is entered, as an Array, after I select the range "In 1 not 2". I know
this works on a smaller list. How can I get it to work on this large file?

Thanks.

 

[Max]

Try this simpler, faster, non-array alternative
Assume List1 in col A, List2 in col B, data from row 2 down
Place in C2: =IF(A2="","",--ISNUMBER(MATCH(A2,B:B,0)))
Copy C2 down to the last row of data in col A

Col C will return:
1 - for items in List1 found in List2
0 - for items in List1 not found in List2
blank ("") - if List1 contains any blank cells in-between

Kill the formulas in col C with an "in-place" copy n paste special as values
Then just apply autofilter on col C to easily filter out results as desired

 

[JHL]

Try this simpler, faster, non-array alternative
Assume List1 in col A, List2 in col B, data from row 2 down
Place in C2: =IF(A2="","",--ISNUMBER(MATCH(A2,B:B,0)))
Copy C2 down to the last row of data in col A

Col C will return:
1 - for items in List1 found in List2
0 - for items in List1 not found in List2
blank ("") - if List1 contains any blank cells in-between

Kill the formulas in col C with an "in-place" copy n paste special as values
Then just apply autofilter on col C to easily filter out results as desired

Thanks Max. Worked like a charm.

 

[Max]

Welcome, JHL