Hi Community!
I'm in need of some advanced parsing tips and tricks 🙂Â
I have a data set from a safety inspection. Each inspection sheet allows you to inspect up to 10 different areas on a single submission. All of the data gets entered into one cell and separated by commas. I've been parsing the data so that if a person inspected 7 areas, the questions and answers for those 7 areas are parsed to match each area. So far everything has been going great until I got to the comments section of the inspection data.
In the attached data set, there are two inspection sheets (identified by the submission number), each with 7 areas. I've already parsed the data into the 7 areas and filtered out only one category for easy viewing. For each area listed, the employee answered Yes or No to whether or not there were health hazards present. You can see the responses before and after parsing. If the employee says Yes, they are then required to enter comments on what hazards they found and what they did to mitigate them. I can't just have a simple formula where IF [Response] = 'Yes' then [Comments] else Null() because there may be more than one set of comments. To show this, I also went ahead and parsed out the Comments. One submission has 2 areas with comments, the other has 3.
The problem I need help with:
If the string of responses is something like, "No,No,No,No,Yes,Yes,No" and I have Comments1 and Comments2 after parsing, I need to make sure the Comments1 comment only shows up next to the first "Yes" in the string, and the Comments2 comment shows up next to the second "Yes" in the string.Â
