trying to compile "other" comments from a survey

Hi! I am trying to compile survey data into a single page. The same survey will be used over and over and the data will keep adding to the data sheet.

I have a few multi-select dropdown columns, which each have an "other" category that then opens up the "other" field in the survey if selected. Often, this is not used.

I am summarizing this data in another sheet, which adds up all the categories. I would also like pull the "other" cells in automatically, and without blanks, so would like the information from "Time for Other" in the raw data to end up as a list in the "other time requests" column in the other sheet, and same with "open feedback"

Thanks!

Andra

Answers

  • Hi @andra_saunders

    One way to do this would be to bring all of the "Time for Other" entries into one cell, using JOIN(COLLECT.

    Ex:

    =JOIN(COLLECT({Time for Other Column}, {Time for Other Column}, <> ""), " / ")

    However if you're looking to parse out the different comments down different rows, the best way to do this would be through using a Report instead of a formula.

    You can filter the Report by the cell in that column not being blank. You could set up a second report for the other column as well, then put both of those Reports into a Dashboard so you can see all comments side-by-side, along with the other numbers you've gathered in this data sheet.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!