How to we exclude a specific name from a DISTINCT LIST USER formula

I would like to exclude a specific user name from this formula.

The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain users, that are no longer in the group.

For example, how do I say to exclude user "Joe Black" from this list?

=IFERROR(INDEX(DISTINCT({Trial Lead}), [Unique Row ID]@row, 0), "")

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Filippo

You can use a COLLECT Function to filter results, for example:

=IFERROR(INDEX(DISTINCT(COLLECT({Trial Lead}, {Trial Lead}, <> "Joe Black")), [Unique Row ID]@row, 0), "")

An alternative would be to use a Report and Group by the Trial Lead column, ignoring out the users you don't want by adding them to the filter criteria in the Report.

Cheers,

Genevieve

Paul Newcome

Looks like I missed a closing parenthesis. Sorry about that.

=IFERROR(INDEX(DISTINCT(COLLECT({Project Lead}, {Project Lead}, AND(@cell <> "Joe Black", @cell <> "Jane Doe"))), [Unique Row ID]@row), "")

All comments

Lucas Rayala

hi @Filippo try this:

=IFERROR(IF([Trial Lead]@row = "Joe Black", "", INDEX(DISTINCT({Trial Lead}), [Unique Row ID]@row, 0)), "")

Filippo

Nice try Lucas. The issue with this formula, is that the data is coming from another sheet.

The data for "{Trial Lead}" is coming from a different sheet.

While the "[Unique Row ID}" is a helper column in the same sheet I am working on, to auto-number each row.

Genevieve P.

Hi @Filippo

You can use a COLLECT Function to filter results, for example:

=IFERROR(INDEX(DISTINCT(COLLECT({Trial Lead}, {Trial Lead}, <> "Joe Black")), [Unique Row ID]@row, 0), "")

An alternative would be to use a Report and Group by the Trial Lead column, ignoring out the users you don't want by adding them to the filter criteria in the Report.

Cheers,

Genevieve

Paul Newcome

You would need to include a COLLECT function to filter and then an AND function to create the list of users to skip.

=IFERROR(INDEX(DISTINCT(COLLECT({Trial Lead}, {Trial Lead}, AND(@cell <> "Joe Black", @cell <> "John Smith", @cell <> "Jane Doe")), [Unique Row ID]@row, 0), "")

Filippo

@Paul Newcome the formula provided by Genevieve P. above worked, but it is only good to exclude 1 user. If I have to exclude other users, I have tried to implement the "AND function", as you have indicated, but I get an error saying "#INCORRECT ARGUMENT SET". I bet there is something small to adjust.....what do you think I need to correct?

GOOD Formula for 1 user - need ADD function for multiple users:

=IFERROR(INDEX(DISTINCT(COLLECT({Project Lead}, {Project Lead}, <>"Joe Black")), [Unique Row ID]@row, 0), "")

This is the formula that I had tried, but it gives me the error message (#INCORRECT ARGUMENT SET):

=IFERROR(INDEX(DISTINCT(COLLECT({Project Lead}, {Project Lead}, AND(@cell <> "Joe Black", @cell <> "Jane Doe")), [Unique Row ID]@row, 0), ""))

Paul Newcome

Looks like I missed a closing parenthesis. Sorry about that.

=IFERROR(INDEX(DISTINCT(COLLECT({Project Lead}, {Project Lead}, AND(@cell <> "Joe Black", @cell <> "Jane Doe"))), [Unique Row ID]@row), "")

Filippo

Works perfectly, thank you Paul and Genevieve!!!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.