• 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

T

tanwarrahul1988

Member
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
 
herofox

herofox

Active Member
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.
 
John Jairo V

John Jairo V

Well-Known Member
Hi to all!
Another option could be:
PHP: 
=SORT(UNIQUE(TOCOL(FILTER(D2:D72,ISERR(-LEFT(D2:D72))),1)))
Blessings!
 
P

Peter Bartholomew

Well-Known Member
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:
 
You must log in or register to reply here.
Top