Help with if statement and multirow formula

Question

Hello -

I have a dataset that contains "stacked" related data. I am trying to use a multi-row if statement to correctly identify any stacked rows that are associated with the relevant data. For example, I want to identify if a string of text begins with "SP" and if so then label that row with a "YES" as well as the next 3 rows with "YES". Once identified, I am planning on unstacking the data using techniques described here by the community.

My multi-row formula with a StartsWith([Data],"SP") correctly identifies my desired first row, but I'm unsure of the best way to bring the "YES" identifier down to rows 2-4. I am not sure if I can have multiple expressions in the "then" portion of the if statement.

Any thoughts on how to accomplish this? Thanks in advance.

Janus · Accepted Answer

Hi,

If I understand your problem correctly, this would be one of the logic to implement your request.

CODE in MULTIROW FORMULA tool :

iif(startswith([Data],"SP") or startswith([Row-1:Data],"SP") or startswith([Row-2:Data],"SP"),"YES",null())