Doubt on creating formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1841401%22%20slang%3D%22en-US%22%3EDoubt%20on%20creating%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1841401%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20that%20answers%20to%20the%20follow%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20unique%20numbers%20in%20ID%20and%20CITY%20if%20%22answer%20for%20ACTIVITY%201%22%20OR%20%22answer%20for%20ACTIVITY%202%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20i%20make%20a%20formula%20to%20achieve%20this%20conclusion%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EID%20(person)%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ECITY%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EACTIVITY%20ONE%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EACTIVITY%20TWO%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E12345%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3Enumber1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E12345%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3Enumber2%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E12345%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3Edone%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E12345%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3Enumber1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E12345%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3Edone%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E12345%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3Enumber1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E67890%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3Enumber1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E67890%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3Edone%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E67890%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3Enumber2%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECarla%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1841401%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-1841472%22%20slang%3D%22en-US%22%3ERe%3A%20Doubt%20on%20creating%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1841472%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853020%22%20target%3D%22_blank%22%3E%40cferr7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGuessing%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DFILTER(A2%3AB10%3B(C2%3AC10%26lt%3B%26gt%3B%22%22)%2B(D2%3AD10%26lt%3B%26gt%3B%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1841522%22%20slang%3D%22en-US%22%3ERe%3A%20Doubt%20on%20creating%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1841522%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%2C%20you%20missed%20the%20word%20%22unique%22%20in%20the%20question%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DUNIQUE(FILTER(A2%3AB10%2C(C2%3AC10%26lt%3B%26gt%3B%22%22)%2B(D2%3AD10%26lt%3B%26gt%3B%22%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Dear all,

 

I am trying to create a formula that answers to the follow:

 

- unique numbers in ID and CITY if "answer for ACTIVITY 1" OR "answer for ACTIVITY 2"

 

 

How can i make a formula to achieve this conclusion?

 

ID (person)CITYACTIVITY ONEACTIVITY TWO
12345Anumber1 
12345Anumber2 
12345A done
12345Anumber1 
12345A done
12345Anumber1 
67890Bnumber1 
67890B done
67890Cnumber2 

 

Thank you in advance!

 

Carla

4 Replies

@cferr7 

Guessing:

=FILTER(A2:B10;(C2:C10<>"")+(D2:D10<>""))

@Detlef Lewin , you missed the word "unique" in the question

=UNIQUE(FILTER(A2:B10,(C2:C10<>"")+(D2:D10<>"")))

@cferr7 

Another variant is PivotTable if filter empty values

image.png