Hi everyone
I've been tasked with transforming the output of a lengthy skills matrix related Google form. A chunk of the survey invites respondents to state their skill proficiency level in relation to a skill. Responses are a checkbox format ranging from N/A, 1, 2 and 3.
Unfortunately, the design of the survey hasn't mitigated for colleagues submitting more than one response per skill, when there should only be one.
Example below:
| Name | Requirements Management | Scenario Analysis | Strategy Analysis | Gap Analysis | Impact and Root Cause Analysis |
| Colleague A | 3 | 2 | 3 | 2 | 1 |
| Colleague B | 1, 2 | 3 | N/A, 1 | 1 | N/A, 1, 2 |
| Colleague C | 1 | 1 | 2 | 2 | 2 |
Ideally, all responses should look something like Colleague A and Colleague C's responses.
How might I build a check or mechanism to highlight / bring to the survey owner's attention, that Colleague B's responses need validating with them?
My initial thoughts were to use the Formula tool to check the relative column if a comma is present, and is so, to produce a table with those values and names of the respective colleagues? If this sounds viable, please can someone help me create a formula?
Notes
- There are around 30~50 skills in total in the survey
- The data type is string - I have used a Multi-field formula tool to convert N/As into zeroes and change the data types to numeric fields (this tool fails when there is more than one response in the data present and produces a 0)
Thanks,
KA
