How to Return only nonblank values from column

Options

Formulas make up the "Make This" column. How would I achieve the "Into This" column???

Thanks

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @BrianDN

    You could use the INDEX function to look at the values in your "Raw" column and return the first distinct value, then the second distinct value, and so on, however this would require a helper column to let the formula know if it's looking for the First, Second, Third, etc.

    For example:

    =IFERROR(INDEX(DISTINCT(COLLECT(Raw:Raw, Raw:Raw, <>"")), Helper@row), "")


    This collects only distinct values from the Raw column, as long as the cell is not blank. Then the reference to the Helper@row at the end of the formula looks at the number in the Helper column to determine which one to bring back.

    My formula open is currently looking at the seventh value in the Raw list, but there isn't a 7th value, so the IFERROR makes the cell turn Blank instead of giving an error.

    Let me know if that makes sense and works for you!

    Cheers,

    Genevieve

Answers

  • Ipshita
    Ipshita ✭✭✭✭✭✭
    edited 02/22/23
    Options

    Hello @BrianDN - Are you trying to return the same values in the "Into This" column as "Make This"? If so, a simple =[Make This]@row formula in the "Into This" column would return the same value as the Make This column. Also, is your Make This column converted to column formula?

    A JOIN formula also works if you have multiple rows with values where you only want to return values that are non-blanks from all of those rows combines. For example, if you have a column called RESULTS and your values are set up under Make This and Into This columns, using the following formula would give your result -

    =JOIN([Make This]@row:[Into This]@row) +""

    Hope this helps!

    Cheers!

    Ipshita

    Ipshita Mukherjee

  • BrianDN
    BrianDN ✭✭
    Options

    Hi Ipshita,

    Thanks for getting back so quickly.

    =[Make This]@row would simply duplicate the column.

    What I want are for the values from Make This to be duplicated into the Into This column but without the blank rows that separate the values.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @BrianDN

    I hope you're well and safe!

    Try something like this. (remember to enable the wrap feature)

    =JOIN(COLLECT([Make This]:[Make This], [Make This]:[Make This], <>""), CHAR(10))

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • BrianDN
    BrianDN ✭✭
    Options

    Hi Andree,

    Thanks,

    However, I entered your formula and drug down. Per the results below you did capture all of the desired data in the first row, which could work it there was a way to transpose it to look like the "Desired" column.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @BrianDN

    Excellent!

    Happy to help!

    You have to enable the Wrap feature.


    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • BrianDN
    BrianDN ✭✭
    Options

    Yes wrapping does display very nicely,

    But I need the values is separate cells since I will then be building formulas; using them as criteria.

    Is there a way to have them in separate cells?

  • BrianDN
    BrianDN ✭✭
    Options

    Hi Andree,

    Thank you for your previous reply.

    Is there a way however, to do this in reverse???

    To split that "Stacked" cell into multiple columns with each stacked value in a different column?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @BrianDN

    You could use the INDEX function to look at the values in your "Raw" column and return the first distinct value, then the second distinct value, and so on, however this would require a helper column to let the formula know if it's looking for the First, Second, Third, etc.

    For example:

    =IFERROR(INDEX(DISTINCT(COLLECT(Raw:Raw, Raw:Raw, <>"")), Helper@row), "")


    This collects only distinct values from the Raw column, as long as the cell is not blank. Then the reference to the Helper@row at the end of the formula looks at the number in the Helper column to determine which one to bring back.

    My formula open is currently looking at the seventh value in the Raw list, but there isn't a 7th value, so the IFERROR makes the cell turn Blank instead of giving an error.

    Let me know if that makes sense and works for you!

    Cheers,

    Genevieve

  • BrianDN
    BrianDN ✭✭
    Options

    Hi Genevieve,

    This works FANTASTICALLY!!! Thank you for getting back to me.

  • cghallo_WBD
    Options

    Hi @Genevieve P.! I tried replicating your formula for my use case but think I'm missing something. As IA Compliance20-24 will only have one value, I'm trying to grab that value into the green cell which is the summary of that section. What do you think?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @cghallo_WBD

    Are you looking to see how many of the cells below have the word "Compliant" in them? What about using a combination of a COUNT formula and text to surface this data:

    =COUNTIF([IA Compliance]20:[IA Compliance]24, "Compliant") + "/5 Compliant"


    For your current cell data this should produce: "1/5 Compliant"

    Does that make sense?

  • cghallo_WBD
    Options

    Thanks. I'm not looking for a count. IA Compliance rows 20 through 24 (one of them) will include either compliant or non-compliant. I just want the green cell to pull whichever it is as the others would remain blank. Does that make sense?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Thanks for clarifying, @cghallo_WBD!

    I would still use a COUNTIF function in this instance as it will be the easiest way to check those 5 cells.

    Try an IF statement like this:

    =IF(COUNTIF([IA Compliance]20:[IA Compliance]24, "Compliant") > 0, "Compliant", IF(COUNTIF([IA Compliance]20:[IA Compliance]24, "Non-Compliant") > 0, "Non-Compliant", ""))


    This looks to see if any of the cells say "Compliant", and if they do, they'll return the same value. Otherwise, if no cells say "Compliant", it will look for "Non-Compliant". If none of the cells have either word, it will return a blank value.

    Cheers,

    Genevieve

  • cghallo_WBD
    Options

    Ah, got it! Yes that works, thank you @Genevieve P. !

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Awesome! Thanks for letting me know it worked 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!