excel - extracting value given an array-CodePudding

I wanted to get the value in column A which does not have a certain value on column B. For example, in this picture, given the set in column B when grouped using the value in column A, how can I get the values which does not have "4" in each set?

So the answer that I'm looking for is 2000 and 3000 because they do not have "4" in their respective sets in column B.

Is this achievable? I can't wrap around my head if INDEX and MATCH can do this.

CodePudding user response：

OK well I see that @JvdV reached the same answer as me which is always reassuring. I added a test for blank cells so:

=UNIQUE(FILTER(A1:A20,(COUNTIFS(A1:A20,A1:A20,B1:B20,4)=0)*(A1:A20<>"")))

You could invert the logic if you wanted to but it's a bit longer:

=UNIQUE(FILTER(A1:A20,(COUNTIFS(A1:A20,A1:A20,B1:B20,"<>"&4)=COUNTIF(A1:A20,A1:A20))*(A1:A20<>"")))

CodePudding user response：

=LET(range,A1:B13,
     ca,INDEX(range,,1),
     cb,INDEX(range,,2),
     ua,UNIQUE(ca),
     ub,UNIQUE(cb), 
     all,DROP(REDUCE(0,ub,LAMBDA(x,u,VSTACK(x,ua&u))),1), 
     alla,DROP(REDUCE(0,ub,LAMBDA(x,u,VSTACK(x,ua))),1),
FILTER(alla,ISERROR(XMATCH(all,ca&cb))))

This doesn't require the 4 as part of the formula and would work on multiple missing numbers

It takes range A1:B13 and divides it into ca (column A) and cb (column B). And the unique values of these two ranges ua (unique values column A) and ub (unique values column B).

all lists all possible joined combinations of ua and ub. alla creates the same list, but without joining the ub values to the outcome.

If we then filter alla where the match of all list to joined ca and cb values throws an error (is missing) you get the required result.

In example below I extended the range and 4000 is missing 1:

It repeats the same value if it's missing multiple numbers. Wrap the FILTER in UNIQUE to avoid that if undesired.

You may want to include the missing value as well:

=LET(range,A1:B17,
     ca,INDEX(range,,1),
     cb,INDEX(range,,2),
     ua,UNIQUE(ca),
     ub,UNIQUE(cb),
     all,DROP(REDUCE(0,ub,LAMBDA(x,u,VSTACK(x,ua&u))),1),
     alla,DROP(REDUCE(0,ub,LAMBDA(x,u,VSTACK(x,HSTACK(ua,u)))),1), 
     a,
FILTER(all,ISERROR(XMATCH(all,ca&cb))),

DROP(REDUCE(0,a,LAMBDA(b,c,VSTACK(b,HSTACK(LEFT(c,4),RIGHT(c,LEN(c)-4))))),1))