Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# Can Excel generate a unique code from response patterns?

Copper Contributor

# Can Excel generate a unique code from response patterns?

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?

2 Replies

# Re: Can Excel generate a unique code from response patterns?

@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)``

# Re: Can Excel generate a unique code from response patterns?

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.