Filter distinct values only into new column

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(INDEX(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), "")

I actually recommend putting this on a different sheet and using a cross sheet reference, but the idea is still the same.

sawuzie

@AravindGP Thank you!

sawuzie

@Paul Newcome thank 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. 👍️

KLZolali

https://community.smartsheet.com/discussion/comment/386641#Comment_386641

@Paul Newcome I'm trying to use this formula as a cross sheet reference to pull in unique week labels but am erroring out. My formula is as follows: =IFERROR(INDEX(DISTINCT(COLLECT({Construction Update Tracker - Week Count}, {Construction Update Tracker - Week Count}, @cell <> "")), {Construction Update Tracker - P-0121 Range 2}), "")

I'm wondering if the issue is with the row index reference for the index part of the formula? Any suggestions?

KLZolali

https://community.smartsheet.com/discussion/comment/386583#Comment_386583

@Paul Newcome I'm trying to use your suggested formula as a cross sheet reference but am erroring out. My formula is below - any suggestions as to what I'm doing wrong? Thanks!

=IFERROR(INDEX(DISTINCT(COLLECT({Construction Update Tracker - Week Count}, {Construction Update Tracker - Week Count}, <>"")), {Construction Update Tracker - P-0121 Range 2}), "")

Paul Newcome

@KLZolali That last {Range} should not be a cross sheet reference.

KLZolali

https://community.smartsheet.com/discussion/comment/414471#Comment_414471

@Paul Newcome Thanks for the response! I changed the formula to the following and I'm still not having any luck:

=IFERROR(INDEX(DISTINCT(COLLECT({Construction Update Tracker - Week Count}, {Construction Update Tracker - Week Count}, @cell <> "")), [Week Label]@row), "")

Paul Newcome

@KLZolali Are you using the helper column as previously described?