• 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

A

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
 
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
 
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.
 
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
 
You must log in or register to reply here.
Back
Top