CODEWITHSUNDEEP
excelexcel-formula
Chapin Cheney
Chapin Cheney

Reputation: 3

How to extract unique values from an array EXCEPT specified values?

I am already abel to extract unique values, in excel, from an array using this function:

{=INDEX(list,MATCH(0,COUNTIF(uniques,list),0))}

However, I want to specify certain values for excel not to return. Is there any way to specify values that I don't want to be found within the already specified "list"? The ideal outome would be something like this:

excel list example

I am also using excel version 2101.

Any information is helpful, thanks!

Upvotes: 0

Views: 2061

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

From your example, I ASSUME you want to exclude the lines starting with Round.

Try:

=LET(x,UNIQUE(List),FILTER(x,LEFT(x,5)<>"Round"))

or

=UNIQUE(FILTER(List,(LEFT(List,5)<>"Round")))

I'm not sure if it is more efficient to filter a smaller list, as is done in the first formula; or to avoid using LET as is done in the second formula.

EDIT

This can also be done using FILTERXML and TEXTJOIN which should be present in all Windows versions 2016+

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,list)&"</s></t>","//s[not(starts-with(.,'Round')) and not(preceding-sibling::*=.)]")

the xPath

  • not(starts-with(.,'Round')) : should be obvious
  • Return only unique values:
    • and not(preceding-sibling::*=.) : do not return a node if any preceding-sibling matches the current node being tested

Upvotes: 1

Related Questions