Filter distinct values only into new column
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!
Best Answers

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.
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

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

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.
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

Thank you!

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

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.
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

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.

@AravindGP Thank you!


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

That worked like a charm, thank you!

Happy to help. 👍️

@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  P0121 Range 2}), "")
I'm wondering if the issue is with the row index reference for the index part of the formula? Any suggestions?

@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  P0121 Range 2}), "")

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

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

@KLZolali Are you using the helper column as previously described?
Help Article Resources
Categories
Check out the Formula Handbook template!