I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over "Distinct" to only give me the unique values of [(Cells Linked from TE Tracker)]
From what I've seen, there is no direct function that can do this, but has anyone figured out a work around to achieve this?
There's no direct way to do this. However, you can add a checkbox helper column to identify duplicates. You can use the formula =IF(COUNTIF([Cells Linked from TE Tracker]:[Cells Linked from TE Tracker], [Cells Linked from TE Tracker]@row) > 1, 1, 0). This will add a check in the duplicates column for all values which appear more than once. You can then add the Distinct column with the formula =IF([Duplicate]@row=0, [Cells Linked from TE Tracker]@row). The Distinct column will only have the distinct values listed.
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training
=IFERROR(INDEX(DISTINCT(COLLECT([(Cells Linked from TE Tracker)]:[(Cells Linked from TE Tracker)], [(Cells Linked from TE Tracker)]:[(Cells Linked from TE Tracker)], @cell <> "")), [Helper Column]@row), "")
There's no direct way to do this. However, you can add a checkbox helper column to identify duplicates. You can use the formula =IF(COUNTIF([Cells Linked from TE Tracker]:[Cells Linked from TE Tracker], [Cells Linked from TE Tracker]@row) > 1, 1, 0). This will add a check in the duplicates column for all values which appear more than once. You can then add the Distinct column with the formula =IF([Duplicate]@row=0, [Cells Linked from TE Tracker]@row). The Distinct column will only have the distinct values listed.
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training
I tried your formula, and it did work as you intended, but I was also hoping that the cells are duplicates could still be listed in the distinct column but only once, since this process doesn't include duplicates at all. I'm hopeful that there is a workaround to accomplish this.
The only workaround is to have the duplicate checkbox a limited row search instead of a whole column reference. This is not the best solution though. An interim alternate would be =IF(COUNTIF([Cells Linked from TE Tracker]1:[Cells Linked from TE Tracker]10, [Cells Linked from TE Tracker]@row) > 1, 1, 0)
Since the formula is looking at a limited data set, some values will not be tagged as duplicate and would appear. When you drag the formula down, Smartsheet will change the reference for row#2 as =IF(COUNTIF([Cells Linked from TE Tracker]2:[Cells Linked from TE Tracker]11, [Cells Linked from TE Tracker]@row) > 1, 1, 0) and so on.
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training
You don't need a checkbox column, but a helper column is definitely helpful. My suggestion would be to use a text/number column and manually enter the numbers 1 through whatever number you think the max is plus a little buffer. So if you think there will never be any more than 100 distinct values, I would suggest going to 125 just in case. Then you can use
= IFERROR(指数(从TE Trac收集(((细胞有关ker)]:[(Cells Linked from TE Tracker)], [(Cells Linked from TE Tracker)]:[(Cells Linked from TE Tracker)], @cell <> ""), [Helper Column]@row), "")
I actually recommend putting this on a different sheet and using a cross sheet reference, but the idea is still the same.
=IFERROR(INDEX(DISTINCT(COLLECT([(Cells Linked from TE Tracker)]:[(Cells Linked from TE Tracker)], [(Cells Linked from TE Tracker)]:[(Cells Linked from TE Tracker)], @cell <> "")), [Helper Column]@row), "")
Paul Newcome ✭✭✭✭✭✭
sawuzie ✭✭
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/2UZI5G6FRBIB\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
