• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

find unique names

Hello Guys I am finding a formula to find unique names but not show the name which contains a number like this "6. Sandhya Enterprises"
So, when I try this below formula it shows the below example results. Pls help me guys
=UNIQUE(SORT(FILTER(D2:D73,D2:D73<>"")))

Examples :

6. Sandhya Enterprises
7. Nirmal Enterprises
8. Aradhya Enterprises
9. Sunny
Dilip
Party
Self A/C
 
Please upload an excel file showing what is required accurately in order to avoid wasting the time of everyone who looks at your participation without benefit or importance.
 
Hi to all!
Another option could be:
PHP:
=SORT(UNIQUE(TOCOL(FILTER(D2:D72,ISERR(-LEFT(D2:D72))),1)))
Blessings!
 
This is the @p45cal solution. Only the styling is different. Firstly, I do not ever use direct cell references, I avoid the practice of relative referencing and I use the locally-scoped LET variable to expand the formula to avoid nesting and explain the intermediate calculations until there is no further benefit in terms of readability. Doing all that, I got
Code:
= LET(
    isNumbered?, ISNUMBER(VALUE(LEFT(party,1))),
    isNonBlank?, party<>"",
    isData?,     party<>"party",
    criterion?,  isData? * isNonBlank? * NOT(isNumbered?),
    matches,     FILTER(party,criterion?),
    SORT(UNIQUE(matches))
  )
The only thing I haven't done here is encapsulate the formula using LAMBDA to conceal the unnecessary detail. That would have given
Code:
= ListPartiesλ(party)
Code:
 
Back
Top