How to Return only nonblank values from column

BrianDN

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

Thanks

Genevieve P.

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

Ipshita

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

BrianDN

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å

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!

BrianDN

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å

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

BrianDN

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

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.

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

Hi Genevieve,

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

cghallo_WBD

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.

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

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.

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

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

Genevieve P.

Awesome! Thanks for letting me know it worked 🙂