-1

I am trying to filter a one larger range from a smaller range. The master range has duplicate entries, however, so when I try to filter from it, it pulls out all duplicates when I'd rather have it filter out only the number it should (preserving the remaining duplicate entries). For instance, if my master range is: Jon, Jim, Jim, Frank, and I want to filter Jim and Frank from that range, I'd like to have Jon and Jim (the second instance of Jim) remain.

Here is the formula I've been using (but it filters out all instances):

=IFERROR(FILTER(C2:M2, ISNA(MATCH(C2:M2, C3:H3,0))),"")

Here is the link to my spreadsheet.

0
=FILTER(B2:M2,ISNA(HLOOKUP(COLUMN(B2:M2),MATCH(B3:E3,B2:M2,0)+1,1,0)))
  • It worked, but then I stretched the row a little further and it seems to lose its function. Did that formula allow scaling of the ranges? – Bardo Oct 6 '17 at 0:08
  • Thank you. I absolutely seek to understand but I do not understand your formula, despite having dismantled it (I thought I did but then made changes and didn't get the results I was anticipating). I created another tab in that same spreadsheet with an extended range. Could you edit that formula so I can see what adjustments you made so as to learn how it can scale? – Bardo Oct 9 '17 at 0:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.