Finding unique values from Column, then listing them in another sheet

Frank S.
Frank S. ✭✭✭✭✭✭

Here is my problem:

I have a column that has multiple different dates listed. I want a formula that will allow me to find the unique dates and then list just the unique dates in a different column.

I have done a lot of research and I know I need a helper column but I'm struggling with the examples I have found.

Any suggestions or help would be appreciated.

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

Best Answers

«13

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Try: =JOIN(DISTINCT(COLLECT([date]:[date], [date]:[date], ISDATE(@cell), char(10))))

    Replace [date] with the name of your date column.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Frank S.
    Frank S. ✭✭✭✭✭✭

    Mark,

    I appreciate the help but I'm not sure I'm following you.

    Do I include the formula in my original sheet or can I use it as a metric?

    Here is what I have:

    =JOIN(DISTINCT(COLLECT({ONE Program Change Request Backlog Range 1}, {ONE Program Change Request Backlog Range 1}, ISDATE(@cell), CHAR(10)))) and I get #Incorrect Argument

    Suggestions?

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The formula error itself is from a misplaced parenthesis.


    =JOIN(DISTINCT(COLLECT({ONE Program Change Request Backlog Range 1}, {ONE Program Change Request Backlog Range 1}, ISDATE(@cell))), CHAR(10))


    If you needed them listed out as dates, it will require additional helper columns and one of the parsing solutions. I will dig through my notes later this evening to see if I can find them. They are also out here somewhere in the Community if you wanted to do a search for them in the meantime.

  • Frank S.
    Frank S. ✭✭✭✭✭✭

    Mark/Paul,

    What I'm looking for is help setting up the helper column and then getting the dates to list.

    I have look through the forums and have not been successful getting my helper column to work correctly.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    HERE is a link to a sheet that contains a parsing solution. The columns to the left of the grey bar are notes, and the columns to the right of the grey bar are what you would actually set up. Does that help?

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @Frank.Smith

    you would need a column with a Row #. (you can manually create this by drag filling the numbers, create as many rows as you think you will need).

    Then you would add the below as a column formula to another column.

    =IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Change Request Backlog Range 1}, {ONE Program Change Request Backlog Range 1}, ISDATE(@cell))), [Row #]@row), "")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Leibel S Haven't seen you in a while. Hope all is well.


    I completely forgot you had come up with that formula. I'm bookmarking this thread so I can come back to it. The only downside I can see to it is if you have a ton of rows the cross-sheet reference(s) could bog things down a little bit quicker, but overall I definitely like this approach better. Thanks for chiming in!

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    It has been awhile :). doing well thank god

  • Frank S.
    Frank S. ✭✭✭✭✭✭

    @Leibel S That formula worked great.

    Now if I wanted to check for a unique number what would change. The number I'm looking for is a build number that is typically something like 2.4.6

    I tried is "isnumber" in place of "isdate" but it did not work.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @Frank.Smith

    Because you have periods in your number you would need to substitute them out. The criteria that should work for this case would be:

    AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A")))

    Let me know how that goes :)

  • Frank S.
    Frank S. ✭✭✭✭✭✭

    @Leibel S

    Please forgive me, but I'm not sure where I would add the additional part of the formula.

    Can you provide me with a little clarification.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @Frank.Smith

    In you original post it seemed like you were trying to get a list of unique dates. Are you now looking to get a unique set of build numbers?

    If yes, the full formula (adding to my previous post) would be:

    =IFERROR(INDEX(DISTINCT(COLLECT({REFERENCE TO BUILD NUMBERS}, {REFERENCE TO BUILD NUMBERS}, AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A")))), [Row #]@row), "")

    Make sure to replace the correct sheet reference.

  • Frank S.
    Frank S. ✭✭✭✭✭✭

    @Leibel S

    Here is the formula I'm using: =IFERROR(INDEX(DISTINCT(COLLECT({REFERENCE TO BUILD NUMBERS}, {REFERENCE TO BUILD NUMBERS}, AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A")))), [Row #]@row), "")) and I get #INCORRECT ARGUMENT SET

    Suggestions?

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    You need to create the cross sheet references, as mentioned in my comment.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!