This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have an excel file that contains data from three surveys I sent out. The first 16 columns contain the questions from survey 1. From column 17-34 contains the questions form survey 2, and columns 35-74 contains the questions from survey 3. The issue I am facing is that the rows (records) is not sorted to users who took survey 1, followed by surveys 2 and 3. It is all mixed up, so the first 10 may be users who took survey 1, row 11 may be survey 3 user, and row 15 may be a user who took survey 3. What I want to be able to do is to run through the data, and if there is a value in any field within the first 16 columns, then output that data to display "Survey 1 User". If there is a value in any field for columns 17-34 then output that data to display "Survey 2 user" and if there is a value in any field for columns 35-74, then display "Survey 3 User". Should I do this using a formula tool, or is there a better way? Also, i'm not entirely sure how to go about this using the formula tool. Any help would be much appreciated. Thanks!
I am not 100% clear on the ask. Can you provide some sample data? Are you trying to create single records for a user who might have data in survey 1 and survey 3. Like, your data might currently have 2 records for user 1 who took surveys 1 and 3?
So as you can see from the sample data I sent, Row 2 and 8 contains data for users who took survey 1, rows 3 & 5 contain data for users who took survey 2, and rows 4,6,and 7 contain data for users who took survey 3. I want to output the data into three different outputs. If there is a value in any field for columns Q1 to Q16, that should be output 1 for users who took survey 1. If there is a value in any field for columns Q17 to Q34, that should be output 2 for users who took survey 2. If there is a value in any field for columns Q35 to Q74, that should be output 3 for users who took survey 3. It's a little confusing but I hope my explanation helps!
There are a bunch of ways to tackle this, but just given the response, I think that I would do something like the attached. It's dynamic but does require a knowledge of the range of questions for each survey. This would allow you to just change the ranges you want to assign to which survey and add surveys and ranges and the like.