Complex Array sorting issue
 

I have a range of Question responses from a survey that I'm seeking to find
the most common response combinations from. Simply sorting the data doesnt
acheive that succesfully

Data is in the following format in a table 100 rows deep by 53 colums wide

Respondent Q1 Q2 Q3.... Q53
R1 1,1,0,1...1
R2 0,1,0,1,..0
R3 0,0,1,.....0
R4 1,1,1,.....0
R5 1,0,1,0...1
....
R100 0,0,0,1,..0

What I am seeking to work out is what are the Largest 10 combinations and
the Question numbers that make that combination - not just a ranked count of
the responses
Eg Top Combination is Q1 and Q2, and Q53

I'm working thru using arrays but can't find a suitable method to get a
result so far

Any guidance appreciated warmly - I have to repeat this over multiple other
sets of similar data and would like to do it via a reusable approach.

Complex Array sorting issue
 

hi, !

I can't see any difference on using "just a ranked count of responses"
and then use an index(ed) locacion for the questions -?-

- Ranking Data In List (and associated topics)
http://www.cpearson.com/Excel/rank.aspx

(perhaps) if you provide a more detailed example of yor expected results (where and why) ?

hth,
hector.

__ OP __
I have a range of Question responses from a survey that
I'm seeking to find the most common response combinations from.
Simply sorting the data doesnt acheive that succesfully
Data is in the following format in a table 100 rows deep by 53 colums wide
Respondent Q1 Q2 Q3.... Q53
R1 1,1,0,1...1
R2 0,1,0,1,..0
R3 0,0,1,.....0
R4 1,1,1,.....0
R5 1,0,1,0...1
...
R100 0,0,0,1,..0
What I am seeking to work out is what are the Largest 10 combinations
and the Question numbers that make that combination
- not just a ranked count of the responses Eg Top Combination is Q1 and Q2, and Q53
I'm working thru using arrays but can't find a suitable method to get a result so far
Any guidance appreciated warmly
I have to repeat this over multiple other sets of similar data and would like to do it via a reusable approach.

Complex Array sorting issue
 

Sure

if data was as follows:

Q1,Q2,Q3,Q4
1,1,0,0
1,1,0,1
1,0,1,0
1,0,0,0


Then result set would be
Q1,Q2,Q4 is largest group
Q1,Q2 is most common group
Q1,Q3 is next common group

I am looking to show the Most Common groups in the full set of data. Ranked
count tells me how many responses I got but not the Q Numbers that are
related.

Make sense ?
Tubster

"Héctor Miguel" wrote:

hi,

I can't see any difference on using "just a ranked count of responses"
and then use an index(ed) location for the questions -?-

- Ranking Data In List (and associated topics)
http://www.cpearson.com/Excel/rank.aspx

(perhaps) if you provide a more detailed example of yor expected results (where and why) ?

hth,
hector.

__ OP __
I have a range of Question responses from a survey that
I'm seeking to find the most common response combinations from.
Simply sorting the data doesnt acheive that succesfully
Data is in the following format in a table 100 rows deep by 53 colums wide
Respondent Q1 Q2 Q3.... Q53
R1 1,1,0,1...1
R2 0,1,0,1,..0
R3 0,0,1,.....0
R4 1,1,1,.....0
R5 1,0,1,0...1
...
R100 0,0,0,1,..0
What I am seeking to work out is what are the Largest 10 combinations
and the Question numbers that make that combination
- not just a ranked count of the responses Eg Top Combination is Q1 and Q2, and Q53
I'm working thru using arrays but can't find a suitable method to get a result so far
Any guidance appreciated warmly
I have to repeat this over multiple other sets of similar data and would like to do it via a reusable approach.

Complex Array sorting issue
 

hi, !

please, excuse me, but I can't get the criteria you used to consider...
- the "largest group"
- the "most/next common group"

is the row-position of the answers relevant for the criteria ? (if so... how/why ?)

didn't you find any helpful tip in the suggested page ?
- Ranking Data In List (and associated topics)
http://www.cpearson.com/Excel/rank.aspx

regards,
hector.

__ OP __
if data was as follows:

Q1,Q2,Q3,Q4
1,1,0,0
1,1,0,1
1,0,1,0
1,0,0,0

Then result set would be
Q1,Q2,Q4 is largest group
Q1,Q2 is most common group
Q1,Q3 is next common group

I am looking to show the Most Common groups in the full set of data.
Ranked count tells me how many responses I got but not the Q Numbers that are related.

Make sense ?

__ OP __
I can't see any difference on using "just a ranked count of responses"
and then use an index(ed) location for the questions -?-

- Ranking Data In List (and associated topics)
http://www.cpearson.com/Excel/rank.aspx

(perhaps) if you provide a more detailed example of yor expected results (where and why) ?

__ OP __
I have a range of Question responses from a survey that
I'm seeking to find the most common response combinations from.
Simply sorting the data doesnt acheive that succesfully
Data is in the following format in a table 100 rows deep by 53 colums wide
Respondent Q1 Q2 Q3.... Q53
R1 1,1,0,1...1
R2 0,1,0,1,..0
R3 0,0,1,.....0
R4 1,1,1,.....0
R5 1,0,1,0...1
...
R100 0,0,0,1,..0
What I am seeking to work out is what are the Largest 10 combinations
and the Question numbers that make that combination
- not just a ranked count of the responses Eg Top Combination is Q1 and Q2, and Q53
I'm working thru using arrays but can't find a suitable method to get a result so far
Any guidance appreciated warmly
I have to repeat this over multiple other sets of similar data and would like to do it via a reusable approach.

Complex Array sorting issue
 

Group = a subset set of valid Question responses eg answer was "1"
Largest Group = Largest set of validQuestion responses = the most common
grouping of question responses where the answer was "1" for all Questions
answered
The challenge is how to show what the questions were tho

The suggested page is like many other resources in this area and only
considers a simple data set. Its easy to show distribution of answers - I'm
looking to show distribution of sets of answers

"Héctor Miguel" wrote:

hi, !

please, excuse me, but I can't get the criteria you used to consider...
- the "largest group"
- the "most/next common group"

is the row-position of the answers relevant for the criteria ? (if so... how/why ?)

didn't you find any helpful tip in the suggested page ?
- Ranking Data In List (and associated topics)
http://www.cpearson.com/Excel/rank.aspx

regards,
hector.

__ OP __
if data was as follows:

Q1,Q2,Q3,Q4
1,1,0,0
1,1,0,1
1,0,1,0
1,0,0,0

Then result set would be
Q1,Q2,Q4 is largest group
Q1,Q2 is most common group
Q1,Q3 is next common group

I am looking to show the Most Common groups in the full set of data.
Ranked count tells me how many responses I got but not the Q Numbers that are related.

Make sense ?

__ OP __
I can't see any difference on using "just a ranked count of responses"
and then use an index(ed) location for the questions -?-

- Ranking Data In List (and associated topics)
http://www.cpearson.com/Excel/rank.aspx

(perhaps) if you provide a more detailed example of yor expected results (where and why) ?

__ OP __
I have a range of Question responses from a survey that
I'm seeking to find the most common response combinations from.
Simply sorting the data doesnt acheive that succesfully
Data is in the following format in a table 100 rows deep by 53 colums wide
Respondent Q1 Q2 Q3.... Q53
R1 1,1,0,1...1
R2 0,1,0,1,..0
R3 0,0,1,.....0
R4 1,1,1,.....0
R5 1,0,1,0...1
...
R100 0,0,0,1,..0
What I am seeking to work out is what are the Largest 10 combinations
and the Question numbers that make that combination
- not just a ranked count of the responses Eg Top Combination is Q1 and Q2, and Q53
I'm working thru using arrays but can't find a suitable method to get a result so far
Any guidance appreciated warmly
I have to repeat this over multiple other sets of similar data and would like to do it via a reusable approach.

Complex Array sorting issue
 

hi, !

(I guess) the next step is upload in a hosting server a sample workbook (skydrive ?)
(even if you need to hard-type your result range for grouping questions/answers)

regards,
hector.

__ OP __
Group = a subset set of valid Question responses eg answer was "1"
Largest Group = Largest set of validQuestion responses = the most common grouping of question responses
where the answer was "1" for all Questions answered
The challenge is how to show what the questions were tho

The suggested page is like many other resources in this area and only considers a simple data set.
Its easy to show distribution of answers - I'm looking to show distribution of sets of answers