Can Excel generate a unique code from response patterns?

Question

I am working with survey response data within an Excel worksheet. Most of the survey answers are words, or a binary 1/0 (indicating whether or not the respondent selected the response).&nbsp;I need to create a new variable in a new column.&nbsp; Can excel generate a new "code" by scanning columns of categories for occurrences of 1s across each row?&nbsp;I have a table of survey data with 10 columns, each of which represents one of ten preference options, and 434 rows, each of which represents one survey responseCOLUMNS&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AF.&nbsp; &nbsp;AG.&nbsp; &nbsp; AH.&nbsp; &nbsp;AI&nbsp; &nbsp;AJ.&nbsp; &nbsp;AK.&nbsp; &nbsp;AL.&nbsp; &nbsp;AM.&nbsp; &nbsp;AN.&nbsp; &nbsp;AO.&nbsp; &nbsp; &nbsp;Respondent 1 replies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp;0Respondent 2 replies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;Ideally, we would have a new column AP that contains a new code that represents the particular sequence of that person's preference responses. So, perhaps Respondent 1's from above would look something like "112031415160708091100 " [1 (column 1)marked 1, 2 (column 2)marked 0, and so on through the 10 columns which is marked 0, or '100'&nbsp;Would I nest a SEQUENCE formula within a SUM(IFNUMBER formula?Am i overthinking this?&nbsp;&nbsp;

harun24hr · Answer

ibitarJr&nbsp;You do not mention what problem you are facing to gain your desired output. Give a try on below formula-=CONCAT(SEQUENCE(,10)&amp;B2:K2)&nbsp;&nbsp;

ibitarjr · Answer

hI&nbsp;Harun24HR&nbsp;thanks for speedy reply. &nbsp;formula returned a #name? error (I had to change cell references from B2:K2 to AF2:AO2&nbsp;I need a way to group survey respondents into groups by their preference responses. Once grouped, I can use this group as an independent variable in linear regression analysis against the other categorial variables.&nbsp;&nbsp;

Forum Discussion

Can Excel generate a unique code from response patterns?

Share

Resources