Find unique values in two uncorrelated columns or sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1511000%22%20slang%3D%22en-US%22%3EFind%20unique%20values%20in%20two%20uncorrelated%20columns%20or%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1511000%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%20(or%201%20column%20in%20two%20sheets%20originally)%3A%3C%2FP%3E%3CTABLE%20width%3D%22229%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22103%22%3ENamePass%3C%2FTD%3E%3CTD%20width%3D%22126%22%3ENameMFA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Euser1%3C%2FTD%3E%3CTD%3Euser2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Euser2%3C%2FTD%3E%3CTD%3Euser3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Euser3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Euser4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20end%20up%20with%20unique%20values%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2264%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3EResult%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3Euser1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3Euser4%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHighlighting%20original%20unique%20values%20in%20original%20ColumnA%20(NamePass)%20is%20acceptable%20as%20well.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeas%3F%26nbsp%3B%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1511000%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1511789%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20unique%20values%20in%20two%20uncorrelated%20columns%20or%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1511789%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723232%22%20target%3D%22_blank%22%3E%40Jasonf450%3C%2FA%3E%26nbsp%3B%20if%20you%20have%20the%20new%20arrray%20functions%20in%20your%20version%20of%20Excel%20it%20should%20be%20something%20as%20easy%20as%20%3DUNIQUE(range)%3C%2FP%3E%3CP%3Ebut%20since%20I%20do%20NOT%20have%20these%20functions%20yet%20here%20is%20my%20formula%20that%20I%20used%20in%20another%20example%20where%20the%20range%20to%20search%20was%26nbsp%3B%24C%243%3A%24H%2416%20and%20the%20Unique%20list%20was%20in%20Col%20J%20starting%20at%20J5%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(OFFSET(%24A%241%2CMOD(AGGREGATE(15%2C7%2C%20(ROW(%24C%243%3A%24H%2416)%2B100000*COLUMN(%24C%243%3A%24H%2416))%2F(--(COUNTIF(J%245%3AJ5%2C%24C%243%3A%24H%2416)%3D0))%2C%201)%2C100000)-1%2CINT(AGGREGATE(15%2C7%2C%20(ROW(%24C%243%3A%24H%2416)%2B100000*COLUMN(%24C%243%3A%24H%2416))%2F(--(COUNTIF(J%245%3AJ5%2C%24C%243%3A%24H%2416)%3D0))%2C%201)%2F100000)-1)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513242%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20unique%20values%20in%20two%20uncorrelated%20columns%20or%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513242%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723232%22%20target%3D%22_blank%22%3E%40Jasonf450%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20516px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204504iE2C326C8DE229BE5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20D1%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(A2%3AA5%2CCOUNTIF(B2%3AB5%2CA2%3AA5)%3D0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have two columns (or 1 column in two sheets originally):

NamePassNameMFA
user1user2
user2user3
user3 
user4 

 

I want to end up with unique values:

Result
user1
user4

 

Highlighting original unique values in original ColumnA (NamePass) is acceptable as well. 

 

Something like "If A not exists in B, highlight"

Ideas?  Thanks!

1 Reply
Highlighted

@Jasonf450 

As variant

image.png

in D1

=FILTER(A2:A5,COUNTIF(B2:B5,A2:A5)=0)