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

curiouslearner

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!

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), "")

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), "")

curiouslearner

Thank you!

Paul Newcome

Happy to help. 👍️