CODEWITHSUNDEEP
arraysexcelcompare
Steven
Steven

Reputation: 11

Excel find results not in one array from another

Without using VBA, is there any way to find and output the results missing from one array compared to another into cells.

I have two sheets, on Sheet1 is a list of numbers in column B and "N/A" In column J if we are done with them. On Sheet2 is we paste a list of numbers in column B. I need to be able to find the numbers missing from Sheet2 that do not have an "N/A" tag in Sheet1 and output them to column C.

I tried
{=INDEX(Sheet1!B:B,MATCH(1,(Sheet1!B:B<>B:B)*(Sheet1!J:J<>"N/A"),0))}
but (Sheet1!B:B<>B:B) doesn't seem to be doing what i think it should. I understand that the above formula won't give more than 1 result yet. I would need to add something to eliminate the results already in column C, but one step at a time.

Upvotes: 1

Views: 1527

Answers (2)

lb_so
lb_so

Reputation: 146

Assumptions:

  • Fulllist is an array of values, say A1:E10, which I will assume you can reference as A1#
  • Sublist is an array of values in G1:J6, which I will assume you can reference as G1#

Goal:

  • You want to see whether any of the values in the first column of 'Sub List' don't exist in the first column of 'Full list'

Solution:

=Let(fulllist,index(A1#,,1), sublist, index(G1#,,1), result, filter(sublist,NOT(ISNUMBER(XMATCH(sublist,fulllist,0)))),result)

Obviously, to see which ones DO exist in the full list, you would remove the NOT statement and related brackets.

=Let(fulllist,index(A1#,,1), sublist, index(G1#,,1), result, filter(sublist,ISNUMBER(XMATCH(sublist,fulllist,0))),result)

**Caveat --- this works on newer versions of excel which allow for array spillranges.

Upvotes: 1

W. Chang
W. Chang

Reputation: 502

Sheet1!B:B<>B:B compares corresponding cells in two sheets. Unless the numbers appear at the same exact rows in both sheets, your code will not work.

I don't think it is possible to deal with this with array formula, because Match doesn't accept an array as its first argument. But you can enter this in C1 and copy and paste to the whole column:

=IF(ISERROR(MATCH(Sheet1!B1,Sheet2!B:B,0)),IF(Sheet1!J1<>"N/A",Sheet1!B1,""),"")

There will be empty cells though.

Upvotes: 0

Related Questions