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

Question

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:

I am also using excel version 2101.

Any information is helpful, thanks!

Yes, there is. And it is easier in O365 vs earlier versions of Excel. If you need help as to how to do it, I suggest you EDIT your question to include an example of your data (preferably as text that can be copy/pasted into a worksheet), along with your expected results. It might help to read How to create a Minimal, Complete, and Verifiable example. Also include the Excel version you are using, and whether it is Windows or MAC or Online — Ron Rosenfeld
– Ron Rosenfeld, Commented Feb 16, 2021 at 19:23
@RonRosenfeld Thanks for the info, I just made some edits. I couldn't copy and paste the data directly without creating an image link, but I hope this is still useful. — Chapin Cheney
– Chapin Cheney, Commented Feb 16, 2021 at 19:47

Ron Rosenfeld · Accepted Answer · 2021-02-17 15:23:38Z

1

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

edited Feb 17, 2021 at 15:23

answered Feb 16, 2021 at 20:08

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chapin Cheney Over a year ago

Sadly my build of Excel does not include either LET or FILTER. I'm using build 13628.20380 Maybe there is an add-on that I am missing?

Ron Rosenfeld Over a year ago

@ChapinCheney At "About Excel" mine says Version 2101 (Build 13628.20380 Click-to-Run) Current Channel. If I click "About Excel", it then shows Microsoft Excel for Microsoft 365 MSO (16.0.13628.20128) 32-bit Perhaps you have an Enterprise version which hasn't been brought up to date yet?

Ron Rosenfeld Over a year ago

@ChapinCheney See my edit for a formula version not using LET or FILTER or UNIQUE

Chapin Cheney Over a year ago

Thanks so much, this worked perfectly. Took a little playing around to understand completely, but honestly thank you

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related