• 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.

Unique values from an unsorted column meeting a certain criteria

AK

New Member
Hello


I need to derive sorted unique values from column. For the purpose I use the following formula from cell A3 below onwards entered as an array formula:


INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF(A$2:$A2, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0))


This formula gives me the desired results.


However, now i want to derive sorted unique values which contain a string e.g. ABC


If the column values are:

ABC-001

DEF-001

GHI-003

ABC-001

ABC-002

GHI-008


I want to derive only the values:

ABC-001

ABC-002


Could anyone please help?


Thanks
 

Hui

Excel Ninja
Staff member
AK

In Excel 2003 use

Filter, Dont Set any criteria, then

Advanced Filter and set the Criteria Range to ="=ABC*"

Set Copy to Another Location and Unique Records only
 

AK

New Member
Hello Hui


I do not want any manual step. I want to use formulae so that any change/update to the List automatically updates the derived list.
 

Clarity

New Member
AK


If you use Chandoo's sort blog and then limit it to lines starting with "ABC" this will hopefully acheive what you are after.


http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/


Myles
 
Top