Conditional search and return

My "Start" column has dates for each row; the "Primary" column has ~20 unique values and the sheet has ~80 rows with no parent rows; the "Task" column is mostly unique in each row instance. I am trying to return the minimum "Start" value to an "Order" column based on some keywords in the "Task" column for every unique "Primary" group, and when there are no keywords present for the group, I would like to return an early date, e.g. 1970-01-01.

I have had no luck with combinations of 'IF(),' 'MIN(),' 'MATCH(),' and 'INDEX()' -- I could theoretically do this with an SQL-esque type query, but am unsure how do so or if that is possible.

Any tips?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would start by suggesting a MIN/COLLECT combo, but I can't be sure without seeing an example. Are you able to provide a screenshot of your sheet with sensitive/confidential data removed, blocked, and/or replaced with mock data as needed?

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would start by suggesting a MIN/COLLECT combo, but I can't be sure without seeing an example. Are you able to provide a screenshot of your sheet with sensitive/confidential data removed, blocked, and/or replaced with mock data as needed?

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • The collect function achieved what I needed in conjunction with a helper column. Thanks, Paul!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!