SOLVED

List out people who like only a particular fruit

%3CLINGO-SUB%20id%3D%22lingo-sub-1627136%22%20slang%3D%22en-US%22%3EList%20out%20people%20who%20like%20only%20a%20particular%20fruit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1627136%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20know%20the%20name(s)%20of%20the%20people%20who%20like%20only%20a%20particular%20fruits.%20Please%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1627136%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-1627198%22%20slang%3D%22en-US%22%3ERe%3A%20List%20out%20people%20who%20like%20only%20a%20particular%20fruit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1627198%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F779111%22%20target%3D%22_blank%22%3E%40Aley1690%3C%2FA%3E%26nbsp%3BAttached%20is%20one%20way%20to%20do%20it%20(which%20only%20works%20if%20you%20have%20an%20Office%20365%20subscription)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1627208%22%20slang%3D%22en-US%22%3ERe%3A%20List%20out%20people%20who%20like%20only%20a%20particular%20fruit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1627208%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F779111%22%20target%3D%22_blank%22%3E%40Aley1690%3C%2FA%3E%26nbsp%3BAs%20a%20variant%2C%20filtering%20%22people%20who%20only%20like%20one%20particular%20fruit%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-09-02%20at%2010.36.39.png%22%20style%3D%22width%3A%20465px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216028i9805902438904C2A%2Fimage-dimensions%2F465x217%3Fv%3D1.0%22%20width%3D%22465%22%20height%3D%22217%22%20title%3D%22Screenshot%202020-09-02%20at%2010.36.39.png%22%20alt%3D%22Screenshot%202020-09-02%20at%2010.36.39.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1640187%22%20slang%3D%22en-US%22%3ERe%3A%20List%20out%20people%20who%20like%20only%20a%20particular%20fruit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1640187%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much!%20Worked%20Perfectly%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1640977%22%20slang%3D%22en-US%22%3ERe%3A%20List%20out%20people%20who%20like%20only%20a%20particular%20fruit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1640977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F779111%22%20target%3D%22_blank%22%3E%40Aley1690%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETEXTJOIN%20can%20also%20be%20useful%20for%20filtering%20null%20returns.%26nbsp%3B%20Basic%20form%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20TEXTJOIN(%22%2C%20%22%2C1%2CIF(ISNUMBER(FIND(%5B%40Fruits%5D%2CFruits))%2C%20Name%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%20with%20a%20full%20set%20of%20365%20go-faster%20stripes%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20SelectedNames%2C%20IF(ISNUMBER(FIND(%5B%40Fruits%5D%2CFruits))%2C%20Name%2C%22%22)%2C%0A%20%20SortedNames%2C%20%20%20SORT(SelectedNames)%2C%0A%20%20TEXTJOIN(%22%2C%20%22%2C%201%2C%20SortedNames))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22fruit.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216871iB67C2166BABAD179%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22fruit.jpg%22%20alt%3D%22fruit.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I want to know the name(s) of the people who like only a particular fruits. Please help

 

4 Replies
Best Response confirmed by Aley1690 (New Contributor)
Solution

@Aley1690 Attached is one way to do it (which only works if you have an Office 365 subscription)

@Aley1690 As a variant, filtering "people who only like one particular fruit".

 

Screenshot 2020-09-02 at 10.36.39.png

 

Thank you so much! Worked Perfectly @Jan Karel Pieterse 

@Aley1690 

TEXTJOIN can also be useful for filtering null returns.  Basic form

 

= TEXTJOIN(", ",1,IF(ISNUMBER(FIND([@Fruits],Fruits)), Name,""))

 

or with a full set of 365 go-faster stripes

 

= LET(
  SelectedNames, IF(ISNUMBER(FIND([@Fruits],Fruits)), Name,""),
  SortedNames,   SORT(SelectedNames),
  TEXTJOIN(", ", 1, SortedNames))

 

 

 fruit.jpg