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

2

Answers

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

    @Leibel S

    Formula Update: =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), ""))

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

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

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

    @Leibel S

    Here is my new formula, I copied the wrong one, in my first message, my apologies.

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

    Still get the same error.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

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

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

    @Leibel S

    I copied over the wrong formula, my apologies.

    Here is the correct formula:

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

    Still get the same error.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

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

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

    @Leibel S

    Sorry for the multiple posts, was having some connectivity issues.

    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

    Can you send screenshots of the 2 sheets?

    As well as the cross sheet references you created.

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

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

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

    @Leibel S

    Yeah, that worked. I was pulling my hair our trying to see where I was missing something.

    You are the best!

    I have another question about the earlier date formula is there a way I can only display dates that are today or earlier?

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

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

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

    @Leibel S

    I jumped the gun, when I put in the formula I get all blank cells.

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

    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 sure you have dates today and earlier?

    Works great by me.

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

    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 ✭✭✭✭✭✭

    none of those dates are today or earlier

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

    @Leibel S


    You are correct, my bad. Now I feel real dumb.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

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

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

    @Leibel S

    So if I want dates today or in the future, just change <= to >= correct?

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!