How to select the right row based on a value of another row's column value.

[JPSeagull]

Hi Everyone. I am stuck. I had spent the day trying to solve this challenge.

I have data group of 3 rows. Each row is casted in the database regardless if there is data beyond customer demographics. My challenge is to evaluate the response of Function ID 1 and if the Function Value is "No" to select Function ID 2 and all of the data on the same row to the right. If the answer of Function ID 1 is "Yes'' then select Function ID 3. I thought I could transpose everything, but I need to figure out how to flag which rows to keep based on the conditions described above. Also, the data is messy as sometimes someone will input responses for both Function ID 2 and 3. And sometimes there is no data for Function ID 1, but there is data on 2 or 3. My mocked up data looks like this:

How the data looks now. Green highlighted rows is what I need to keep

The results I am trying to get is this below. Note that  I would like to recast Function ID 2 and 3 to be 1A.

[usmanbashir · Accepted solution]

Hey @JPSeagull,  I think I was able to replicate your results. Let me know if this is what you were looking for!

[JPSeagull]

@usmanbashir - Thank you. I had something close that, but it wasn't bringing and I see what I needed. That formula (tool 10) was what I was missing. Thank you again! Hope all is well.