Check for more than one response to a question in a Google form dataset

[arundaka02]

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

[PhilipMannering · Accepted solution]

Hi @arundaka02 

that's probably what I'd do too. Count the commas for number of responses. See an example attached.

[arundaka02]

This is brilliant - thank you, @PhilipMannering