Can I identify a specific permutation of responses to a survey and display it?

[Aaragorn]

I very much appreciate any and all help in accomplishing this objective as follows:

I have an online survey which has 9 list box questions. The only options for each question is "A" or "B". When the form is submitted I will have one row with some "9 column permutation" of "A's" and "B's". Each column receives the responses for one question. Column A collects answers for question 1, Column B collects answers for question 2, Column C collects answers for question 3, etc .... up to column "I". Each row is an individual respondent. From these 9 questions I generate a profile of the respondent.

I need to generate a data table of all the possible permutations of "AAAAAAAAA" (9 index values) which could be either "A" or "B" and pair them with the profile they identify in a table. Then I need to match the individual respondent's survey answers to this table and the corresponding profile that their answers align with in the table by matching the permutation in the table with theirs. Then I need to show which profile they are matched with using a simple text description of two or three words (found in the table) in column "K" of the same row as their survey answers.

I hope this description is sufficient. To recap then how this should look:

I survey 9 questions which are placed in columns "A" thru "I" one row per respondent.
Each question will be answered by only either "A" or "B".
I concatenate those 9 responses in column "J" producing some permutation of "AAAAAAAAA" (9 index values).
I match the 9 responses permutation in column "J" with the correlating permutation in the listed data table (located somewhere else in the workbook or worksheet) which identifies the profile of that individual and lastly...
I return that profile identifier from the data table in column "K' on the same row as the respondent's answers.

The row will appear like this:

A B A A A B B A B ABAAABBAB Profile Type 1


The data table will appear somewhere out of the way in the workbook or worksheet like this:

AAAAAAAAA Profile Type 1
AAAAAAAAB Profile Type 2
AAAAAAABB Profile Type 3
AAAAAABBB Profile Type "x"
(etc. etc. listing all possible permutations with their paired profile type)

This is the overview of the project. I don't know how to code it it's just a little beyond my current coding skill level. I have used macros and I have used cell formulas before and I have some coding experience but not enough. I don't know if the best approach to this is with scripts or cell formulas. I am collecting the data using a google docs spreadsheet and I don't know if that has the computing versatility to handle this or if I will have to import the responses into excel and then process the responses.

I realize this is an excel forum so I am happy to get the solution in what works in excel spreadsheets but if anyone knows if the googledocs spreadsheets can run the same code or scripts or whatever and help me eliminate any manual processing steps from this I would appreciate it. I am looking for the most "code elegant" solution you know something effective and concise.

Thanks in advance I really can't do this alone.

 

[Skybluekid]

On sheet 2 have the following

A B
AAAAAAAAA Profile Type 1
AAAAAAAAB Profile Type 2
AAAAAAABB Profile Type 3
AAAAAABBB Profile Type "x"


on sheet 1

use =INDEX(Sheet2!B:B,MATCH(A1&B1&C1.....&I1,Sheet2!A:A,0)) Use Control Shift Enter

As for J and K can you post some data to see what results are expected

 

[Apocolyse]

I'm still not sure what your's result should be.

"I match the 9 responses permutation in column "J" with the correlating permutation in the listed data table (located somewhere else in the workbook or worksheet) which identifies the profile of that individual and lastly...
I return that profile identifier from the data table in column "K' on the same row as the respondent's answers"

I still don't know what to do with column "J" & "K" when finished column "I". For column "I" you just need a formula such as: =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1). For column "J" & "K" where the data comes from? from other table? can u make an excel file as an example?

 

[tlowry]

Well,

Here is what I came up with. You need a way to turn the AB strings into a type number to do statistical analysis. So what I suggest is to turn the AB string into a number by treating the A as 0 and the B as 1 creating a unique binary number for each of the possibilities. To do this with Excel, use the User Defined Function (UDF) below. This will give a unique number for each of the possible combinations. (0 through 511) If you are a true geek, or know one, you can do binary calculations on these numbers.

I’ll give detailed instructions:

• Have all the data in one sheet with one of the columns containing the AAAAAAAAA designation (Column 5 in the example)
• Go into development mode (alt F11)
• Insert a Module (Insert – Module)
• Copy and paste the code below into the module
• in the data work sheet choose an empty column and type:

=GetTypeNumber(E1)
in the first cell of the column

• Copy the UDF for the rest of the column

Excel 2000

	E	F
AAAAAAAAB
BAAAAAAAB
BBBBBBBBB

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]257[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]511[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]F1[/TH]
[TD="align: left"]=GetTypeNumber(E1)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]F2[/TH]
[TD="align: left"]=GetTypeNumber(E2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]F3[/TH]
[TD="align: left"]=GetTypeNumber(E3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Function GetTypeNumber(sResp) As Long
    Dim sBin As String
    sBin = Replace(UCase(sResp), "A", "0")
    sBin = Replace(UCase(sBin), "B", "1")
    GetTypeNumber = Bin2Dec(sBin)
End Function
Function Bin2Dec(sMyBin As String) As Long
    Dim x As Integer
    Dim iLen As Integer
    iLen = Len(sMyBin) - 1
    For x = 0 To iLen
        Bin2Dec = Bin2Dec + _
          Mid(sMyBin, iLen - x + 1, 1) * 2 ^ x
    Next
End Function

 

[Aaragorn]

I would upload a spreadsheet but I don't know how to use this forum's tools to get it done.

I did what skybluekid suggested and it did return the correct profile type in column K on sheet 1 as below: (it did also create a #NUM! error in the array value of the index function because I've got text as well as a number in there. In the final version of this I actually want to return a text string as the type not a number.)

[TABLE="width: 793"]
<tbody>[TR]
[TD]Question 1[/TD]
[TD]Question 2[/TD]
[TD]Question 3[/TD]
[TD]Question 4[/TD]
[TD]Question 5[/TD]
[TD]Question 6[/TD]
[TD]Question 7[/TD]
[TD]Question 8[/TD]
[TD]Question 9[/TD]
[TD]Permutation[/TD]
[TD]Profile Type[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]BBBAAAAAA[/TD]
[TD]Type 4[/TD]
[/TR]
</tbody>[/TABLE]

when sheet 2 looks like this:

[TABLE="width: 227"]
<tbody>[TR]
[TD]AAAAAAAAA[/TD]
[TD]Type 1[/TD]
[/TR]
[TR]
[TD]BAAAAAAAA[/TD]
[TD]Type 2[/TD]
[/TR]
[TR]
[TD]BBAAAAAAA[/TD]
[TD]Type 3[/TD]
[/TR]
[TR]
[TD]BBBAAAAAA[/TD]
[TD]Type 4[/TD]
[/TR]
</tbody>[/TABLE]

this does not generate the permutation table on sheet 2 yet.

Is that what tlowry's function code does? would I then have the code on sheet 1 and not need a table or any other sheet? If so that would be elegant. That's a crafty bit of code there tlowry very nice! I can't wait to give it a go... (for what its worth I am running excel 2002 version 10.2614.2625) but wait.... wouldn't I still need a matching table once I have generated the number so that I can assign the number to the profile type? I don't really see what difference it makes to assign the profile type to a generated number as to a concatenated string of 9 index values, both are going to be unique but I still need to assign the profile types to the unique results in a table I'm thinkin'... right? how else would I connect the dots of the profile types to the answers?

 

[Skybluekid]

Google "Dropbox" and download the content to your PC.

Yes you would need somekind of table to pull back the Profile Type

Slight change to my orignal formula

In J1 =CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2)

In K1 =INDEX(Sheet2!B:B,MATCH(J1,Sheet2!A:A,0)), just use normal enter

 

[Aaragorn]

That'll work, I know dropbox, Here is the dropbox link https://www.dropbox.com/sh/h04z936r3kjx6dr/icWvAqyIuh/Profile Type Permutation Sheet.xls
now we can be on the same page LITERALLY

I still get the #NUM! error in Sheet1 K2 but it also still returns the correct result. lol

I really think this is getting close if we can just generate the permutation/profile table now. I have to say, I'm impressed with the blinding speed of the response to my post. You guys rock!

Lest we forget, does anyone know if googledocs spreadsheet will handle these same functions and codes? If I could indeed get this all done right on their site it WOULD be elegant!

 

[Skybluekid]

I cant see where the #NUM! error is in K2?

Do you want excel to generate all the 511 permutations?

One question would be: Would the profile types run sequencial, ie AAAAAAAAA = Type 1, AAAAAAAAB=Type 2 etc? Or are they specific so AAAAAAAAA= Type 23 , AAAAAAAAB= Type 485?

 

[Aaragorn]

Do you want excel to generate all the 511 permutations?

One question would be: Would the profile types run sequencial, ie AAAAAAAAA = Type 1, AAAAAAAAB=Type 2 etc? Or are they specific so AAAAAAAAA= Type 23 , AAAAAAAAB= Type 485?

I think I got it. I thought on this last night and my rework applies a completely different approach because I redid the second sheet with what I believe the website currently doing this survey does to generate one of 8 profile types. I don't actually need to generate all possible combinations I just need to go with a highest probability outcome method.

I need to test my output against the output of the website and see if I am getting it right. I still have to assign 4 values to parameter 3 for questions 2 and 5 on sheet 2. I have saved a revised version of the project in the dropbox folder if you want to see what I did. If I have my parameters assigned correctly I think it should work. I have high confidence in all but the two questions on the 3rd parameter I haven't installed yet. I don't know if there is a more elegant way to do this but right now sheet 2 is doing the matching work and sheet 1 is displaying the result row by row for each submission.

I welcome any suggestions that might make it better, or critiques of my new approach.

 

[Aaragorn]

I cant see where the #NUM! error is in K2?

Do you want excel to generate all the 511 permutations?

One question would be: Would the profile types run sequencial, ie AAAAAAAAA = Type 1, AAAAAAAAB=Type 2 etc? Or are they specific so AAAAAAAAA= Type 23 , AAAAAAAAB= Type 485?

After comparing my output with that of the online output I am trying to match it's clear that I'm not using the same valuation system and I have no clue how they are weighting their answers and doing the tabulation. Therefore it seems I am left with little choice except to actually generate all 512 possible combinations of 9 index values and go thru them one by one.

Can you tell me how to generate these 511 permutations in a column?

omg this link say there are over 300K permutations for a 9 digit number but is that because it's using base 10 and numbers 1 - 9? and I am only using binary "A" and "B" so I only have 511?
http://www.j-walk.com/ss/excel/tips/tip46.htm