Conditional search and return

03/24/21
Accepted

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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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?

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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?

    thinkspi.com

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.