Can Excel generate a unique code from response patterns?

Copper Contributor

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). I need to create a new variable in a new column.  Can excel generate a new "code" by scanning columns of categories for occurrences of 1s across each row?

 

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 response

COLUMNS

         AF.   AG.    AH.   AI   AJ.   AK.   AL.   AM.   AN.   AO.     

Respondent 1 replies           1      0       1     1      1      0      0       0       1       0

Respondent 2 replies           0      1       0     0      1      1      1       1       0       1

 

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'

 

Would I nest a SEQUENCE formula within a SUM(IFNUMBER formula?

Am i overthinking this? 

 

Screen Shot 2022-11-24 at 6.15.09 PM.png

2 Replies

@ibitarJr You do not mention what problem you are facing to gain your desired output. Give a try on below formula-

=CONCAT(SEQUENCE(,10)&B2:K2)

Harun24HR_0-1669348921600.png

 

 

hI @Harun24HR thanks for speedy reply.  formula returned a #name? error (I had to change cell references from B2:K2 to AF2:AO2

 

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.