Create unique list that populates a second sheet (not report)?

Hello, I'm having trouble figuring out the correct formula to populate a list of unique locations and totals their entries on a second sheet. I couldn't find a forum example that doesn't use a report. This needs to be on a second sheet because it will be used in other formulas and dashboards.

Sheet 1 includes a list of locations, activities, and results. It will continue to populate new rows that are generated from a form.

Sheet 2 needs to list the unique locations and total their results:

I've fussed with INDEX/DISTINCT/COLLECT and it's not working. Thank you for your help!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You would insert a text/number column (called "Number" in this example) onto the second sheet and manually populate the numbers 1 - # to prepopulate as many rows as you think you will need plus a buffer. If you anticipate no more than 50 unique entries then I would suggest populating 75 rows.

Then you would use this to pull in the unique list:

=IFERROR(INDEX(DISTINCT(COLLECT({Locations}, {Locations}, @cell <> "")), Number@row), "")

All comments

Paul Newcome

Then you would use this to pull in the unique list:

=IFERROR(INDEX(DISTINCT(COLLECT({Locations}, {Locations}, @cell <> "")), Number@row), "")

curiouslearner

Thank you!

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