Return All Values based on a Filter

Pestomania · 01/30/25

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?

Marcela · 01/30/25

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

L J · 01/30/25

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 · 01/30/25

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 · 01/30/25

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 · 01/30/25

Marcela

Jan 30, 2025

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

This is awesome!!

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

Pestomania · 02/10/25

Marcela

Jan 30, 2025

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

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 · 02/10/25

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 · 02/11/25

Marcela

Feb 10, 2025

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)
https://us.v-cdn.net/6031209/uploads/25P8DP28L3XQ/screenshot-2025-02-10-at-16-43-17.png
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

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?

Formulas and Functions

Formulas and Functions

Return All Values based on a Filter

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Created Date of Row Copied from Another Sheet

Need to update dates to Q1_2025, Q2_2025, Q3_2025, Q4_2025

Writing a reference formula with multiple IF/AND situations