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

04/29/21
Accepted

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.

Best Answers

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭
    Accepted Answer

    @Frank.Smith

    Looks like I was missing a parentheses in my formula.

    The below should be correct:

    =IFERROR(INDEX(DISTINCT(COLLECT({ONE Program Approved Change Requests Range 2}, {ONE Program Approved Change Requests Range 2}, AND(@cell <> "", ISNUMBER(IFERROR(VALUE(SUBSTITUTE(@cell, ".", "")), "A"))))), [Row #]@row), "")

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭
    Accepted Answer

    see below


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

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭
    Accepted Answer

    yes

Previous13

Answers

  • Mark CronkMark 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.SmithFrank.Smith ✭✭✭✭

    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?

  • Paul NewcomePaul 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.

    thinkspi.com

  • Frank.SmithFrank.Smith ✭✭✭✭

    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.

  • Paul NewcomePaul 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?

    thinkspi.com

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭

    @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 NewcomePaul 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!

    thinkspi.com

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭

    It has been awhile :). doing well thank god

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Leibel S Good deal. 👍️

    thinkspi.com

  • Frank.SmithFrank.Smith ✭✭✭✭

    @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.

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭

    @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.SmithFrank.Smith ✭✭✭✭

    @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.

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭

    @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.SmithFrank.Smith ✭✭✭✭

    @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?

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭

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

Sign In or Register to comment.