Duplicate Data combined for distinction

I was reading this help and it has some parts I need and noticed it referenced a sheet that no longer exists.

https://community.smartsheet.com/discussion/69259/formula-to-pull-data-from-one-column-and-combine-duplicates

What I am looking for to get going and just hit a dead end after reading in here for the last few hours. This is a two part question

I need to join duplicated names from one column to a single cell that will be referenced for a Dashboard. Along with the name duplication I need another cell look for the duplicated names and their total point values for another cell.

Please excuse the crude arrows here

All the formulas and methods I am finding are close, but do not work in my application. It might be on how I am phrasing the query to why I am not finding it and am looking for any insight on this.

Thanks again for any and all assistance on this :) cheers


This is the formula I thought would work with everything I have found, but here we are.

*JOIN(COLLECT(DISTINCT(Agent:Agent)))


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would suggest putting this list and calcs on a separate sheet.


    There are a number of posts already out here in the community that detail how to generate a unique list, but the basic idea is that you would use a helper (text/number) column with the numbers 1 through whatever manually entered plus a bit of a buffer just in case. If you think the longest your list will ever be is 100 unique entries, I suggest going to 125. That sort of thing.


    Then you would use an INDEX/DISTINCT/COLLECT to generate the list and then a SUMIFS to get the total points for each.

    =IFFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Agent Column}, {Source Sheet Agent Column}, @cell <> "")), [Helper Column]@row), "")


    =SUMIFS({Source Sheet Points Column}, {Source Sheet Agent Column}, @cell = [Agent Name]@row)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!