Return text when appears in a list but not if in another list

%3CLINGO-SUB%20id%3D%22lingo-sub-1493549%22%20slang%3D%22en-US%22%3EReturn%20text%20when%20appears%20in%20a%20list%20but%20not%20if%20in%20another%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1493549%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20some%20help%20with%20creating%20a%20formula%20that%20returns%20a%20text%20string%20from%20a%20column%20of%20data%20(that%20will%20include%20blanks)%20but%20it%20cant%20also%20be%20included%20in%20another%20column%20of%20data.%20For%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%201%3A%20A%2C%20B%2C%20C%2C%20D%3C%2FP%3E%3CP%3EColumn%202%3A%20A.%20F%2C%20H%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20need%20a%20formula%20to%20return%20only%20text%20values%20of%20F%2C%20H%20(as%20A%20is%20a%20legal%20owner).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20if%20anyone%20could%20please%20provide%20the%20answer%20to%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1493549%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1493554%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20text%20when%20appears%20in%20a%20list%20but%20not%20if%20in%20another%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1493554%22%20slang%3D%22en-US%22%3EThought%20I%20would%20add%20the%20formula%20needs%20to%20generate%20a%20new%20list%20not%20all%20the%20text%20values%20in%20a%20single%20cell%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1493726%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20text%20when%20appears%20in%20a%20list%20but%20not%20if%20in%20another%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1493726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711818%22%20target%3D%22_blank%22%3E%40tofudragon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20are%20both%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%20477px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201673i3E6704435A9C3E2B%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1494270%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20text%20when%20appears%20in%20a%20list%20but%20not%20if%20in%20another%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1494270%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESergei%20thank%20you%20very%20much%20I%20entered%20the%20formula%20and%20its%20working%20except%20for%20one%20problem%20the%20first%20entry%20is%20still%20showing%20in%20my%20list%20as%20per%20the%20screenshot%20below.%20Are%20you%20able%20to%20let%20me%20know%20what%20I%20can%20change%20to%20the%20formula%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1494648%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20text%20when%20appears%20in%20a%20list%20but%20not%20if%20in%20another%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1494648%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711818%22%20target%3D%22_blank%22%3E%40tofudragon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20it%20possible%20to%20provide%20sample%20file%3F%20Screenshot%20doesn't%20give%20all%20information.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I need some help with creating a formula that returns a text string from a column of data (that will include blanks) but it cant also be included in another column of data. For example:

 

Column 1: A, B, C, D

Column 2: A. F, H

 

So I need a formula to return only text values of F, H (as A is a legal owner). 

 

Appreciate if anyone could please provide the answer to this.

5 Replies
Thought I would add the formula needs to generate a new list not all the text values in a single cell
Highlighted
Highlighted

@Sergei Baklan 

 

Sergei thank you very much I entered the formula and its working except for one problem the first entry is still showing in my list as per the screenshot below. Are you able to let me know what I can change to the formula? 

 

Thanks 

 

Highlighted

@tofudragon 

Is it possible to provide sample file? Screenshot doesn't give all information.

Highlighted

@Sergei Baklan 

It turns out there was an error with my formula thanks again for your help it works perfectly.

Now I am having an issue with summing numbers using the list as a criteria in a SUMIFS formula.