nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

Returning Distinct values from Index/Collect

Elizabeth N

Hi all — hoping for some guidance.

Context: I have Segment Names where each Segment Names has several Manager Names. I want to build one sheet for each specific Segment and pull in the unique corresponding manager names. Both the Segment and Manager Names change frequently, but I am bringing them into a Source Sheet ("Execs PI" in the formula below) using a data table / data shuttle connection and then bringing them into the sheet shown below.

My challenge has been returning only the unique Manager Names. My formula is as follows:

=INDEX(COLLECT({Execs PI - L4 Name}, {Execs PI - Seg 4}, $[Segment 4]$1), $[Numbers for Pulling SVP Names]@row)

As you can see below, the manager names are returning for each time they are listed in the Source Sheet instead of just once. I tried incorporating "Distinct" into the formula, but I was missing something as it was not working as I expected.

Any tips / guidance is appreciated! Thank you.

Find more posts tagged with

Formulas

Join

collect

INDEX

index/match

Accepted answers

Paul Newcome

You just need to wrap the COLLECT function in the DISTINCT function.

=INDEX(DISTINCT(COLLECT({Execs PI - L4 Name}, {Execs PI - Seg 4}, $[Segment 4]$1)), $[Numbers for Pulling SVP Names]@row)

All comments

Paul Newcome

You just need to wrap the COLLECT function in the DISTINCT function.

=INDEX(DISTINCT(COLLECT({Execs PI - L4 Name}, {Execs PI - Seg 4}, $[Segment 4]$1)), $[Numbers for Pulling SVP Names]@row)

Elizabeth N

Thanks @Paul Newcome - I actually just got it to work earlier and was coming back here to delete this post. Thank you as always!

Paul Newcome

@Elizabeth N 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, category/bannerUrl, category/bannerImageAlt, category/extensions, category/customizationLabels, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/bannerImageAlt, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.