Creating a column that excludes values if they appear in both of two other columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2352913%22%20slang%3D%22en-US%22%3ECreating%20a%20column%20that%20excludes%20values%20if%20they%20appear%20in%20both%20of%20two%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2352913%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20in%20need%20of%20some%20assistance%2C%20and%20am%20turning%20to%20the%20community%20here%20for%20any%20help%20that%20can%20be%20offered.%20I%20am%20trying%20to%20set%20up%20a%20column%20(D)%20that%20will%20omit%2Fexclude%20the%20value%20for%20a%20column%20(A)%20if%20that%20same%20value%20appears%20in%20another%20column%20(B).%20The%20formula%20we've%20been%20utilizing%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ROWS(%241%3A1)*COUNT(C%3AC)%2C%22%22%2CINDEX(A%3AA%2CSMALL(C%3AC%2CROWS(%241%3A1))))%20i%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Es%20giving%20us%20no%20results%20a.%26nbsp%3BThe%20spreadsheet%20that%20we%20have%20been%20working%20on%20(which%20is%20failing%20miserably)%20is%20attached.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2352913%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2352972%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20column%20that%20excludes%20values%20if%20they%20appear%20in%20both%20of%20two%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2352972%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1051212%22%20target%3D%22_blank%22%3E%40MLowrey70%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(A2%3AA18%2C1-COUNTIFS(B2%3AB6%2CA2%3AA18))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2353017%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20column%20that%20excludes%20values%20if%20they%20appear%20in%20both%20of%20two%20other%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2353017%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1051212%22%20target%3D%22_blank%22%3E%40MLowrey70%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(IF(ISNUMBER(MATCH(A2%2C%24B%242%3A%24B%246%2C0))%2C%22%22%2CA2)%3D0%2C%22%22%2CIF(ISNUMBER(MATCH(A2%2C%24B%242%3A%24B%246%2C0))%2C%22%22%2CA2))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20have%20to%20adjust%20the%20range%26nbsp%3B%24B%242%3A%24B%246%20to%20match%20that%20range%20in%20your%20application.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

I'm in need of some assistance, and am turning to the community here for any help that can be offered. I am trying to set up a column (D) that will omit/exclude the value for a column (A) if that same value appears in another column (B). The formula we've been utilizing 

 

=IF(ROWS($1:1)*COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROWS($1:1)))) i

 

s giving us no results a. The spreadsheet that we have been working on (which is failing miserably) is attached. 

2 Replies

@MLowrey70 

Try this:

=FILTER(A2:A18,1-COUNTIFS(B2:B6,A2:A18))

Hi @MLowrey70 

 

=IF(IF(ISNUMBER(MATCH(A2,$B$2:$B$6,0)),"",A2)=0,"",IF(ISNUMBER(MATCH(A2,$B$2:$B$6,0)),"",A2))

 

you have to adjust the range $B$2:$B$6 to match that range in your application.

 

Cheers