过滤不同值只有新列

sawuzie
sawuzie ✭✭
inFormulas and Functions

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!

· Share on Facebook Share on Twitter

Best Answers

  • AravindGP
    AravindGP ✭✭✭✭
    Answer ✓

    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

    Reach out for any help on licenses, configuration, or training

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

    10xViz.com

    · Share on Facebook Share on Twitter

Answers

  • AravindGP
    AravindGP ✭✭✭✭
    Answer ✓

    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

    Reach out for any help on licenses, configuration, or training

    · Share on Facebook Share on Twitter
  • sawuzie
    sawuzie ✭✭

    Thank you!

    · Share on Facebook Share on Twitter
  • sawuzie
    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

    · Share on Facebook Share on Twitter
  • AravindGP
    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.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

    · Share on Facebook Share on Twitter
  • Paul Newcome
    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.

    10xViz.com

    · Share on Facebook Share on Twitter
  • sawuzie
    sawuzie ✭✭

    @AravindGPThank you!

    · Share on Facebook Share on Twitter
  • sawuzie
    sawuzie ✭✭
    edited 07/24/23

    @Paul Newcomethank you

    I tried that and this is what is returning for me

    Unique.png


    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

    10xViz.com

    · Share on Facebook Share on Twitter
  • sawuzie
    sawuzie ✭✭

    That worked like a charm, thank you!

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help.️

    10xViz.com

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Try this - =\"We are at \" + [% closed rate]@row * 100 + \"% closed rate on ticket status for the month of \"+[Month]@row.<\/p>"},{"commentID":390301,"body":"

I figured it out! Updated formula to get the 2 decimal places as well.<\/p>

=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//m.santa-greenland.com/community/discussion/107888/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"

You were, indeed, very close.<\/p>

=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula\n <\/a>\n<\/div>\n

It looks like you forgot to close out the AND <\/strong>funtion. Try this<\/p>

=IF([Invoice Received?]@row = 0, \"Green\", IF(AND([Renewal Date]@row >= TODAY(0), [Renewal Date]@row <= TODAY(+30)), \"Red\", \"Yellow\"))<\/code><\/p>

Hope this helps!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108832,"type":"question","name":"How do I create a IF(AND formula combining multiple cross references?","excerpt":"In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula: =COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback…","snippet":"In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting…","categoryID":322,"dateInserted":"2023-08-10T23:46:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:12:56+00:00","insertUserID":140084,"insertUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":140084,"lastUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":49,"score":null,"hot":3383486357,"url":"https:\/\/community.smartsheet.com\/discussion\/108832\/how-do-i-create-a-if-and-formula-combining-multiple-cross-references","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108832\/how-do-i-create-a-if-and-formula-combining-multiple-cross-references","format":"Rich","tagIDs":[207,219,254,344,440],"lastPost":{"discussionID":108832,"commentID":390262,"name":"Re: How do I create a IF(AND formula combining multiple cross references?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390262#Comment_390262","dateInserted":"2023-08-11T17:12:56+00:00","insertUserID":140084,"insertUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/9UG82HSGCS9G\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:23:34+00:00","dateAnswered":"2023-08-11T00:00:03+00:00","acceptedAnswers":[{"commentID":390143,"body":"

I think I am following what you are attempting to do. Try this:<\/p>

COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), {Dispute & Chargeback Intake Sheet Type of CB}, \"AMEX\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":207,"urlcode":"Sales","name":"Sales"},{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Is there a formula to show as %

    What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % ="We are at " + [% closed rate]@row + "…

  • IF/AND Formula

    Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from "unparsable" to "incorrect…

  • How do I create a IF(AND formula combining multiple cross references?

    In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula: =COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback…