Duplicate Data combined for distinction

Frank Hammond

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

Paul Newcome

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)

Itai Perez

Hey @Frank Hammond,

I am not sure I understand, you have a long list of tests, where would you like to consolidate the data? in the first rows, the first instance of the 'Agent' or on another sheet?

Can you give an example of an end result you would like to see?

Thank you

Frank Hammond

For sure, Itai.

Refer to the SS I have with the pertinent items outlined.

I need to combine all agent names from the agent column to only show it one time in the consolidated name column. As you can see with Test 1 it shows in the column one time even though it is listed 2 times in the agent column.

Now with that, the Points column I need to consolidate and sum up those points with the corresponding agents in the consolidated name data and placed in the consolidated points data column.

You are correct, this is a long list, and I was hitting a wall just trying to get the name portion together and figured I would ask for a little assistance from anyone that may have done something similar to this in the past.

Paul Newcome

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)

Frank Hammond

Thank you, Paul. I did see the index tutorials and was working with them with this current sheet after I had posted the question as that seemed like the way to go. I like the idea you gave me on this, but is still coming back Unparseable. I did find a reference from 2019 where you had mentioned to someone if the range part was not working to use sheetname - column and that did not work either.

Range 3 is the actual Agent Column and Range 1 is my Index column

Paul Newcome

Your syntax is off. All you should have to change from my formula is each of the {Cross Sheet References} as well as where I have [Helper Column]@row being a reference to the manual entry number column you put in the sheet that houses the INDEX formula.

Frank Hammond

Thanks, Paul. While I assumed the formula was the exact way to write it. I used the Reference Another Sheet on the formula drop down, then selected the column and that is what it produced for those sections. Unless I am totally missing something here.

{Source Sheet Agent Column} is range 3

[Helper Column] is range 1

Frank Hammond

Moreover on this one, Paul. I meant to tell you have the points calculations part working swimmingly, it's just getting the indexing formula to work as intended. Test 1 is indeed 2 points.

Paul Newcome

The helper column does not go on the source sheet. There is no cross sheet reference to the helper column. Only the Agent column.

The helper column only goes on the metrics sheet and is a direct cell reference within the formula.

Frank Hammond

(Backing away slowly) OMG, so dumb of me. It even says it right here. [Helper Column]@row) *Hangs head in shame on that part.

Still getting unparseable for the reset. I am not sure why the cross referencing is not working as intended. I have read through help files on this and this seems pretty straight forward.

What is really odd, is I had zero issues with this one.

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

Points is range 5 and agent is range 3, I modified the one below with range 3 since we are referencing the agent column twice.

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

IFFERROR(INDEX(DISTINCT(COLLECT({Battlepass Range 3}, {Battlepass Range 3}, @cell <> "")), Index@row), "")

Paul Newcome

I'm not sure I follow your last post. Are you able to provide updated screenshots?

Frank Hammond

Sorry about that. Hopefully this clears it up.

For the agent look up, it is still coming back unparseable. We are referencing the Agent Column twice so it is the same range. I corrected the index part to reference the index column on this page now.

The formula for the point consolidation is 100% correct and works as intended with the references. Range 5 is the points Column, and Range 3 is the Agent Column.

Paul Newcome

Looks like I fat fingered an extra "F" in the IFERROR function. Removing one of those should clear it up for you.

Frank Hammond

sigh, I need to get glasses, I did not even ಠ_ಠ the extra F there.

100% works as intended. Thank you again, Paul.

Paul Newcome

Happy to help. 👍️