Displaying first entry and hiding duplicate names on project plan sheet

RSmith30 · 04/19/22

Hello,

@Paul Newcome You have been extremely helpful to others in the forum, so I am hoping to reach out and work with you.

My objective is to have team member names displayed in a rollup/dashboard without excessive manual data entry by project leaders. My thought is that on the project plan sheet, the project leader must assign tasks to team members. I would like to use this column, named "Assigned To", which has contact list properties so automated workflows send notifications, to create a report sheet of just the names, which I could then use the report to display on the dashboard.

I am unable to figure out how to display just the first instance of a name either in a helper column or on another sheet/report since a team member may have many tasks assigned to them. I have tried the following:

Solution 1:

Helper column:

Row 1 is additional header info that is referencing main metadata page.

Row 2 : =COUNT(DISTINCT(Assigned To$2:Assigned To@row))

Row 3 and beyond: =IF(COUNT(DISTINCT(Assigned To$2:Assigned To@row)) <> COUNT(DISTINCT(Assigned To$2:Assigned To2)), COUNT(DISTINCT(Assigned To$2:Assigned To@row)))

I receive unparseable in return

Solution 2:

=IFERROR(IF(LEN([Assigned To]1) = 0, "", IF(COUNTIFS([Assigned To]:[Assigned To], [Assigned To]1) = 1, 0, 1)), 0)

This solution returns the duplicates as 1 and non-duplicates as 0, but I don't know how to then combine them to get just the first instance on a list.

There is proprietary information on the template sheet as I test am not sure how to share the sheet without exposing the information if needed as a reference.

Thank you so much in advance!

RSmith

James Keuning · 04/22/22

OK, scrap everything I told you, because I checked my work, and what I told you to do is not going to take you where you want to go. But there is an easier way.

In your Landing sheet, you have your Row ID, now put this formula in a new column:

=INDEX(DISTINCT({Assigned To}), [Row ID]@row)

Where Assigned To is the column in your foreign sheet. And then wrap that in some error handling to clean it up:

=IFERROR(INDEX(DISTINCT({Assigned To}), [Row ID]@row), "")

I am confident this will work for you. And now you want to understand why. Put this column formula in another column:

=IFERROR(JOIN(DISTINCT({Assigned To}), "; "), "")

This is showing you what this DISTINCT is doing and what INDEX is doing. DISTINCT is grabbing the values and storing them in memory, and INDEX is saying "take the first on in the list" and then "take the second one in the list" and then "take the third..." and so on. And that is what we use the ROWID, so that we are incrementing the 1, 2, 3 etc.

(And that's why we get errors when we get to the end of the unique list, because the index that we built in memory does not go up that high.)

You could also accomplish this by hard coding your formula:

IFERROR(INDEX(DISTINCT({Assigned To}), 1), "") to get the first

IFERROR(INDEX(DISTINCT({Assigned To}), 2), "") to get the second

IFERROR(INDEX(DISTINCT({Assigned To}), 3), "") to get the third

and so on

But we use the ROWID so that we can use a column formula and not have to hand-code all of those formulas.

WARNING: one problem with Row ID is that if you delete a row, you lose that number forever, and now your index won't pull correctly. If that happens, you can delete the Row ID column and re-enter it so that you get a fresh list of 1, 2, 3. You can also just manually create your 1,2,3 column.

James Keuning · 04/22/22

Also, if you really want to be fancy, you can use this column formula in a checkbox column:

=[Row ID]@row - (MAX(COLLECT([Row ID]:[Row ID], [Row ID]:[Row ID], <[Row ID]@row))) = 1

to make sure that your Row ID is always incrementing by 1. And you can create a workflow to alert you if that checkbox is ever FALSE.

RSmith30 · 04/21/22

@Paul Newcome , up bump. :) :)

James Keuning · 04/21/22

I get my unique list of names by using MATCH and SMALL

In my Rollup sheet, I have a field called Increment, and it is just 1, 2, 3, etc. I use this so that I can use a Column Formula in my roll up field:

=IFERROR(INDEX({AttyName}, (MATCH(SMALL({RowID}, Increment@row), {RowID}, 0))), "")

My foreign sheet has a numeric Row ID field. And I think you can take it from there.

The only caveat is that you need to keep the increment field going in the Rollup sheet, and always have enough cells prepped to stay ahead of the unique count of names in the foreign sheet.

RSmith30 · 04/21/22

Hi @James Keuning ,

Thank you so much for the quick response!

That is actually kind of the route I took after perusing the community a bit further. My issue now is that my index formula is unparseable and I cannot figure out why. I am new to Smartsheets and am still learning functionalities (obviously! )

My main source page has a "helper" column that has this formula in it:

=IF(COUNTIFS([Assigned To]$1:[Assigned To]@row, [Assigned To]@row) = 1, COUNTIFS([Assigned To]$1:[Assigned To]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell)))))

The purpose of this is to identify the first instance of a name in the "Assigned To" column and give that row a row number, essentially removing duplicate entries with the unique row identifier (verbiage?).

On my landing sheet I also have a helper column that lists the rows, similar to your "row id". The formula I have in the primary column on the helper sheet contains this formula:

=INDEX({Process Focus Plan Range 1}, LARGE{Process Focus Plan Range 2}, [Helper Column]@row))

where "Process Focus Plan Range 1" is referencing the "Assigned To" column on the source sheet, "Process Focus Plan Range 2" is referencing the helper column on the source page (unique row #'s), and the "helper column" is obviously referencing the helper column on the landing sheet.

I am not sure where I went wrong. Inserting your formula also returns unparseable. I am not sure if I am referencing the columns in the wrong sequence or something else. Using your formula specific to my headers/setup, it reads:

=IFERROR(INDEX({Process Focus Plan Range 1}, (MATCH(SMALL({RowID}, {Process Focus Plan Range 2}@row), {RowID}, 0))), "")

I honestly don't know these formulas truly work being so new so troubleshooting myself is difficult. Thank you again for your kindness to respond. Looking forward to the follow up!

~Ryan

RSmith30 · 04/21/22

@James Keuning

The sheets are just test pages with mumbo jumbo in them to see if functionality is working. Here are screenshots for reference:

Source page:

Landing sheet:

I would expect the unparseable column to produce a list of names similar to the "Assigned To" column on the source page, without the duplicate entries. :)

James Keuning · 04/22/22