Return All Values based on a Filter

Pestomania

Hi,

I have a file with two columns "Process Name" & "Process Type".

I would like to do a unique filter like in Excel. Return all Process Names where Process Type = 3P - Third Party.

While not user friendly, I was able to get this to work for the unique values, but cannot figure out the filter:

Row 1:

=INDEX(DISTINCT({Process Name}), 1)

Row 2:1000:

=IFERROR(INDEX(DISTINCT({Process Name}), 1 + COUNT([Process_Name]$1:[Process_Name]1)), "")

Is there a way to not make this so difficult?

Find more posts tagged with

Accepted answers

Marcela

Hello @Pestomania

You can achieve this by using a combination of COLLECT and JOIN functions for a filter.

Use this formula to return all unique Process Names where Process Type = "3P - Third Party" in a single cell:

=JOIN(COLLECT({Process Name}, {Process Type}, "3P - Third Party"), CHAR(10))

To provide more context:

COLLECT({Process Name}, {Process Type}, "3P - Third Party"):
→ Collects all Process Names where Process Type = "3P - Third Party".
JOIN(..., CHAR(10)):
→ Joins the results into a single cell.

If you want the results in separated rows instead of manually using INDEX in multiple rows, try this approach:

Row 1 Formula:
=INDEX(COLLECT(DISTINCT({Process Name}), {Process Type}, "3P - Third Party"), 1)

Row 2–1000 Formula:
=IFERROR(INDEX(COLLECT(DISTINCT({Process Name}), {Process Type}, "3P - Third Party"), ROW() - ROW($Row$1) +1), "")

You can copy the second formula down as far as needed.

Hope this helps!

Marce

All comments

L J

I may be out to lunch on what you're trying to accomplish and this could be far too simplistic, but could you just use a Report that references your datasheet, and summarize the data in that report?

Sample report:

Set filter to be whatever Process Category you are looking at:

Group by Process Type if you're looking at more than one in the filter above:

Get your counts with the Summary option:

Pestomania

You may be in line with what I am looking to do except whenever I add the grid to my report, it shows no columns to display (there are 46).

Marcela

Hello @Pestomania

You can achieve this by using a combination of COLLECT and JOIN functions for a filter.

Use this formula to return all unique Process Names where Process Type = "3P - Third Party" in a single cell:

=JOIN(COLLECT({Process Name}, {Process Type}, "3P - Third Party"), CHAR(10))

To provide more context:

COLLECT({Process Name}, {Process Type}, "3P - Third Party"):
→ Collects all Process Names where Process Type = "3P - Third Party".
JOIN(..., CHAR(10)):
→ Joins the results into a single cell.

If you want the results in separated rows instead of manually using INDEX in multiple rows, try this approach:

Row 1 Formula:
=INDEX(COLLECT(DISTINCT({Process Name}), {Process Type}, "3P - Third Party"), 1)

Row 2–1000 Formula:
=IFERROR(INDEX(COLLECT(DISTINCT({Process Name}), {Process Type}, "3P - Third Party"), ROW() - ROW($Row$1) +1), "")

You can copy the second formula down as far as needed.

Hope this helps!

Marce

Pestomania

https://community.smartsheet.com/discussion/comment/470931#Comment_470931

This is awesome!!

I wish Smartsheet would make a few edits like array formula flows and conditional formatting for "duplicate values".

Pestomania

https://community.smartsheet.com/discussion/comment/470931#Comment_470931

Hi Marcela,

Well, I cannot get it to work for some reason. I thought it was but now it isn't.

I have this in row 3 (I put some other data in Row 1 & 2) and this is what I have now. I keep getting "#Incorrect Argument Set" now.

=INDEX(COLLECT(DISTINCT({Process_Name_Dallas}), {Process_Type_Dallas}, "3P - Third Party"), 1)

I did get this one to work:

=INDEX(DISTINCT(COLLECT({Process_Name_Dallas}, {Process_Type_Dallas}, "3P - Third Party", {Category_Holder_Dallas}, false)), 1)

but then the second formula becomes #Unparseable

Marcela

I see what you mean

In this case, you need to add a helper column called sequence and change the order of the functions from COLLECT DISTINCT to DISTINCT COLLECT and put sequence@row. See below:

=INDEX(DISTINCT(COLLECT([Process Name]:[Process Name], [Process Type]:[Process Type], "3P - Third Party")), sequence@row)

You can add IFERROR in the cells you get an error when there is no distinct value in the row:

=INDEX(DISTINCT(COLLECT([Process Name]:[Process Name], [Process Type]:[Process Type], "3P - Third Party")), sequence@row)

Let me know if this works for you!

Marce

Pestomania

https://community.smartsheet.com/discussion/comment/472252#Comment_472252

Marcela,

This worked great to get me there!!!

=IFERROR(INDEX(DISTINCT(COLLECT({Dallas Process Name}, {Process_Type}, "3P - Third Party", {Category_Check}, false)), [Row ID]@row), "")

This is more out of curiosity:

Can I have it set up to collect all data in one sheet, once it runs out go to the next sheet and return all data that meets the criteria, and continue?

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.