过滤不同值只有新列

sawuzie

Hello!

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?

Thank you!

AravindGP

Hi@sawuzie

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.

Paul Newcome

Sorry about that. I forgot the DISTINCT function.

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

AravindGP

Hi@sawuzie

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.

sawuzie

Thank you!

sawuzie

Hello again@AravindGP!

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.

Thank you

AravindGP

Hi@sawuzie

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.

Paul Newcome

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.

sawuzie

@AravindGPThank you!

sawuzie

@Paul Newcomethank you

I tried that and this is what is returning for me

Paul Newcome

Sorry about that. I forgot the DISTINCT function.

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

sawuzie

That worked like a charm, thank you!

Paul Newcome

Happy to help.️