turn a matrix into a sorted list google docs/spreadsheets
I've created a large-ish matrix by doing a =pearson( analysis on survey responses in google docs/spreadsheets and would like to convert it into a sorted list.
The matrix has labels (the survey questions) in row 2 and column b. Each intersecting cell has the value. Here's what the formula looks like.
=pearson(FILTER( Pc!$C$2:$AW$999 ; Pc!$C$2:$AW$2= C$2 ),FILTER(Pc!$C$2:$AW$999 ;Pc!$C$2:$AW$2=$B3))
This is what I'd like to get to:
a b c
Question one question 2 correlation
Then sorting by column c is easy.
How can I get all the points out of the matrix/array, along with the labels in this way?
Ideally I'd be able to do this only to points below the diagonal as there of course are dupes above..
Thanks!
arrays matrix google-sheets
edited Jan 21 '18 at 3:40
Mogsdad
33k1186192
asked May 1 '12 at 12:43
heorling
169315
add a comment |
I've created a large-ish matrix by doing a =pearson( analysis on survey responses in google docs/spreadsheets and would like to convert it into a sorted list.
The matrix has labels (the survey questions) in row 2 and column b. Each intersecting cell has the value. Here's what the formula looks like.
=pearson(FILTER( Pc!$C$2:$AW$999 ; Pc!$C$2:$AW$2= C$2 ),FILTER(Pc!$C$2:$AW$999 ;Pc!$C$2:$AW$2=$B3))
This is what I'd like to get to:
a b c
Question one question 2 correlation
Then sorting by column c is easy.
How can I get all the points out of the matrix/array, along with the labels in this way?
Ideally I'd be able to do this only to points below the diagonal as there of course are dupes above..
Thanks!
arrays matrix google-sheets
edited Jan 21 '18 at 3:40
Mogsdad
33k1186192
asked May 1 '12 at 12:43
heorling
169315
Can you share your spreadsheet as "view only", so I can take a look at a sample of the data to understand it better? (e.g. if you don't want to, please create a spreadsheet with bogus data and the same structure)
– Henrique G. Abreu
May 2 '12 at 22:43
Sure, here's a link. link
– heorling
May 3 '12 at 1:07
Will this generate one row for each value under the diagonal? i.e. the combination of all questions against all others? Isn't this table arrangement better to look and find results?
– Henrique G. Abreu
May 3 '12 at 18:48
Such formula is too difficult for me to come up without spending long hours thinking about it. Sorry.
– Henrique G. Abreu
May 3 '12 at 18:59
thanks for the look. yes the idea was to simply rank them by correlation, what you want is simply to know where the strongest links are. I'm thinking you'd have to do it in several steps with fancy indirects but still not sure how to get them all into a single array.. thanks again
– heorling
May 3 '12 at 22:25
add a comment |
I've created a large-ish matrix by doing a =pearson( analysis on survey responses in google docs/spreadsheets and would like to convert it into a sorted list.
The matrix has labels (the survey questions) in row 2 and column b. Each intersecting cell has the value. Here's what the formula looks like.
=pearson(FILTER( Pc!$C$2:$AW$999 ; Pc!$C$2:$AW$2= C$2 ),FILTER(Pc!$C$2:$AW$999 ;Pc!$C$2:$AW$2=$B3))
This is what I'd like to get to:
a b c
Question one question 2 correlation
Then sorting by column c is easy.
How can I get all the points out of the matrix/array, along with the labels in this way?
Ideally I'd be able to do this only to points below the diagonal as there of course are dupes above..
Thanks!
arrays matrix google-sheets
edited Jan 21 '18 at 3:40
Mogsdad
33k1186192
asked May 1 '12 at 12:43
heorling
169315
I've created a large-ish matrix by doing a =pearson( analysis on survey responses in google docs/spreadsheets and would like to convert it into a sorted list.
The matrix has labels (the survey questions) in row 2 and column b. Each intersecting cell has the value. Here's what the formula looks like.
=pearson(FILTER( Pc!$C$2:$AW$999 ; Pc!$C$2:$AW$2= C$2 ),FILTER(Pc!$C$2:$AW$999 ;Pc!$C$2:$AW$2=$B3))
This is what I'd like to get to:
a b c
Question one question 2 correlation
Then sorting by column c is easy.
How can I get all the points out of the matrix/array, along with the labels in this way?
Ideally I'd be able to do this only to points below the diagonal as there of course are dupes above..
Thanks!
arrays matrix google-sheets
arrays matrix google-sheets
edited Jan 21 '18 at 3:40
Mogsdad
33k1186192
asked May 1 '12 at 12:43
heorling
169315
edited Jan 21 '18 at 3:40
Mogsdad
33k1186192
asked May 1 '12 at 12:43
heorling
169315
edited Jan 21 '18 at 3:40
Mogsdad
33k1186192
edited Jan 21 '18 at 3:40
Mogsdad
33k1186192
edited Jan 21 '18 at 3:40
Mogsdad
33k1186192
33k1186192
asked May 1 '12 at 12:43
heorling
169315
asked May 1 '12 at 12:43
heorling
169315
asked May 1 '12 at 12:43
heorling
169315
169315
Can you share your spreadsheet as "view only", so I can take a look at a sample of the data to understand it better? (e.g. if you don't want to, please create a spreadsheet with bogus data and the same structure)
– Henrique G. Abreu
May 2 '12 at 22:43
Sure, here's a link. link
– heorling
May 3 '12 at 1:07
Will this generate one row for each value under the diagonal? i.e. the combination of all questions against all others? Isn't this table arrangement better to look and find results?
– Henrique G. Abreu
May 3 '12 at 18:48
Such formula is too difficult for me to come up without spending long hours thinking about it. Sorry.
– Henrique G. Abreu
May 3 '12 at 18:59
thanks for the look. yes the idea was to simply rank them by correlation, what you want is simply to know where the strongest links are. I'm thinking you'd have to do it in several steps with fancy indirects but still not sure how to get them all into a single array.. thanks again
– heorling
May 3 '12 at 22:25
add a comment |
Can you share your spreadsheet as "view only", so I can take a look at a sample of the data to understand it better? (e.g. if you don't want to, please create a spreadsheet with bogus data and the same structure)
– Henrique G. Abreu
May 2 '12 at 22:43
Sure, here's a link. link
– heorling
May 3 '12 at 1:07
Will this generate one row for each value under the diagonal? i.e. the combination of all questions against all others? Isn't this table arrangement better to look and find results?
– Henrique G. Abreu
May 3 '12 at 18:48
Such formula is too difficult for me to come up without spending long hours thinking about it. Sorry.
– Henrique G. Abreu
May 3 '12 at 18:59
thanks for the look. yes the idea was to simply rank them by correlation, what you want is simply to know where the strongest links are. I'm thinking you'd have to do it in several steps with fancy indirects but still not sure how to get them all into a single array.. thanks again
– heorling
May 3 '12 at 22:25
Can you share your spreadsheet as "view only", so I can take a look at a sample of the data to understand it better? (e.g. if you don't want to, please create a spreadsheet with bogus data and the same structure)
– Henrique G. Abreu
May 2 '12 at 22:43
Can you share your spreadsheet as "view only", so I can take a look at a sample of the data to understand it better? (e.g. if you don't want to, please create a spreadsheet with bogus data and the same structure)
– Henrique G. Abreu
May 2 '12 at 22:43
Sure, here's a link. link
– heorling
May 3 '12 at 1:07
Sure, here's a link. link
– heorling
May 3 '12 at 1:07
Will this generate one row for each value under the diagonal? i.e. the combination of all questions against all others? Isn't this table arrangement better to look and find results?
– Henrique G. Abreu
May 3 '12 at 18:48
Will this generate one row for each value under the diagonal? i.e. the combination of all questions against all others? Isn't this table arrangement better to look and find results?
– Henrique G. Abreu
May 3 '12 at 18:48
Such formula is too difficult for me to come up without spending long hours thinking about it. Sorry.
– Henrique G. Abreu
May 3 '12 at 18:59
Such formula is too difficult for me to come up without spending long hours thinking about it. Sorry.
– Henrique G. Abreu
May 3 '12 at 18:59
thanks for the look. yes the idea was to simply rank them by correlation, what you want is simply to know where the strongest links are. I'm thinking you'd have to do it in several steps with fancy indirects but still not sure how to get them all into a single array.. thanks again
– heorling
May 3 '12 at 22:25
thanks for the look. yes the idea was to simply rank them by correlation, what you want is simply to know where the strongest links are. I'm thinking you'd have to do it in several steps with fancy indirects but still not sure how to get them all into a single array.. thanks again
– heorling
May 3 '12 at 22:25
add a comment |
2 Answers
2
active
oldest
votes
I think I found a solution to placing the combination of the headers in a single column.
It involves a series of auxiliary columns, but works.
Let's say we have a single column with all unique headers on column A. I'll assume it's 6 values. So, on cell B1 we paste:
=ArrayFormula(join(";";A1&","&A2:A$6))
And then copy it down to B5. On C1 we join it all and split making a single column:
=transpose(split(join(";";B1:B5);";"))
If needed, we can split the combination in two columns again on D1
=ArrayFormula(split(C1:C15;","))
I don't know why, but the value on E1 does not work correctly, so I just pasted =A2
With these columns you can easily do your nice Pearson-Filter trick again to have it all in a single column. Hope this helps :)
answered May 25 '12 at 18:20
Henrique G. Abreu
13.6k13851
This is definitely a step in the right direction. I'm woring out the kinks, will let you know!
– heorling
May 25 '12 at 22:14
add a comment |
Maybe something like this will help:
=ArrayFormula(transpose(split(CONCATENATE(transpose(C2:AW999)&char(9)), char(9))))
(C2:AW999 is your data range)
answered Nov 12 '18 at 18:17
Luiz Rodrigo
1
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f10397784%2fturn-a-matrix-into-a-sorted-list-google-docs-spreadsheets%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think I found a solution to placing the combination of the headers in a single column.
It involves a series of auxiliary columns, but works.
Let's say we have a single column with all unique headers on column A. I'll assume it's 6 values. So, on cell B1 we paste:
=ArrayFormula(join(";";A1&","&A2:A$6))
And then copy it down to B5. On C1 we join it all and split making a single column:
=transpose(split(join(";";B1:B5);";"))
If needed, we can split the combination in two columns again on D1
=ArrayFormula(split(C1:C15;","))
I don't know why, but the value on E1 does not work correctly, so I just pasted =A2
With these columns you can easily do your nice Pearson-Filter trick again to have it all in a single column. Hope this helps :)
answered May 25 '12 at 18:20
Henrique G. Abreu
13.6k13851
This is definitely a step in the right direction. I'm woring out the kinks, will let you know!
– heorling
May 25 '12 at 22:14
add a comment |
I think I found a solution to placing the combination of the headers in a single column.
It involves a series of auxiliary columns, but works.
Let's say we have a single column with all unique headers on column A. I'll assume it's 6 values. So, on cell B1 we paste:
=ArrayFormula(join(";";A1&","&A2:A$6))
And then copy it down to B5. On C1 we join it all and split making a single column:
=transpose(split(join(";";B1:B5);";"))
If needed, we can split the combination in two columns again on D1
=ArrayFormula(split(C1:C15;","))
I don't know why, but the value on E1 does not work correctly, so I just pasted =A2
With these columns you can easily do your nice Pearson-Filter trick again to have it all in a single column. Hope this helps :)
answered May 25 '12 at 18:20
Henrique G. Abreu
13.6k13851
This is definitely a step in the right direction. I'm woring out the kinks, will let you know!
– heorling
May 25 '12 at 22:14
add a comment |
I think I found a solution to placing the combination of the headers in a single column.
It involves a series of auxiliary columns, but works.
Let's say we have a single column with all unique headers on column A. I'll assume it's 6 values. So, on cell B1 we paste:
=ArrayFormula(join(";";A1&","&A2:A$6))
And then copy it down to B5. On C1 we join it all and split making a single column:
=transpose(split(join(";";B1:B5);";"))
If needed, we can split the combination in two columns again on D1
=ArrayFormula(split(C1:C15;","))
I don't know why, but the value on E1 does not work correctly, so I just pasted =A2
With these columns you can easily do your nice Pearson-Filter trick again to have it all in a single column. Hope this helps :)
answered May 25 '12 at 18:20
Henrique G. Abreu
13.6k13851
I think I found a solution to placing the combination of the headers in a single column.
It involves a series of auxiliary columns, but works.
Let's say we have a single column with all unique headers on column A. I'll assume it's 6 values. So, on cell B1 we paste:
=ArrayFormula(join(";";A1&","&A2:A$6))
And then copy it down to B5. On C1 we join it all and split making a single column:
=transpose(split(join(";";B1:B5);";"))
If needed, we can split the combination in two columns again on D1
=ArrayFormula(split(C1:C15;","))
I don't know why, but the value on E1 does not work correctly, so I just pasted =A2
With these columns you can easily do your nice Pearson-Filter trick again to have it all in a single column. Hope this helps :)
answered May 25 '12 at 18:20
Henrique G. Abreu
13.6k13851
answered May 25 '12 at 18:20
Henrique G. Abreu
13.6k13851
answered May 25 '12 at 18:20
Henrique G. Abreu
13.6k13851
answered May 25 '12 at 18:20
Henrique G. Abreu
13.6k13851
13.6k13851
This is definitely a step in the right direction. I'm woring out the kinks, will let you know!
– heorling
May 25 '12 at 22:14
add a comment |
This is definitely a step in the right direction. I'm woring out the kinks, will let you know!
– heorling
May 25 '12 at 22:14
This is definitely a step in the right direction. I'm woring out the kinks, will let you know!
– heorling
May 25 '12 at 22:14
This is definitely a step in the right direction. I'm woring out the kinks, will let you know!
– heorling
May 25 '12 at 22:14
add a comment |
Maybe something like this will help:
=ArrayFormula(transpose(split(CONCATENATE(transpose(C2:AW999)&char(9)), char(9))))
(C2:AW999 is your data range)
answered Nov 12 '18 at 18:17
Luiz Rodrigo
1
add a comment |
Maybe something like this will help:
=ArrayFormula(transpose(split(CONCATENATE(transpose(C2:AW999)&char(9)), char(9))))
(C2:AW999 is your data range)
answered Nov 12 '18 at 18:17
Luiz Rodrigo
1
add a comment |
Maybe something like this will help:
=ArrayFormula(transpose(split(CONCATENATE(transpose(C2:AW999)&char(9)), char(9))))
(C2:AW999 is your data range)
answered Nov 12 '18 at 18:17
Luiz Rodrigo
1
Maybe something like this will help:
=ArrayFormula(transpose(split(CONCATENATE(transpose(C2:AW999)&char(9)), char(9))))
(C2:AW999 is your data range)
answered Nov 12 '18 at 18:17
Luiz Rodrigo
1
answered Nov 12 '18 at 18:17
Luiz Rodrigo
1
answered Nov 12 '18 at 18:17
Luiz Rodrigo
1
answered Nov 12 '18 at 18:17
Luiz Rodrigo
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f10397784%2fturn-a-matrix-into-a-sorted-list-google-docs-spreadsheets%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can you share your spreadsheet as "view only", so I can take a look at a sample of the data to understand it better? (e.g. if you don't want to, please create a spreadsheet with bogus data and the same structure)
– Henrique G. Abreu
May 2 '12 at 22:43
Sure, here's a link. link
– heorling
May 3 '12 at 1:07
Will this generate one row for each value under the diagonal? i.e. the combination of all questions against all others? Isn't this table arrangement better to look and find results?
– Henrique G. Abreu
May 3 '12 at 18:48
Such formula is too difficult for me to come up without spending long hours thinking about it. Sorry.
– Henrique G. Abreu
May 3 '12 at 18:59
thanks for the look. yes the idea was to simply rank them by correlation, what you want is simply to know where the strongest links are. I'm thinking you'd have to do it in several steps with fancy indirects but still not sure how to get them all into a single array.. thanks again
– heorling
May 3 '12 at 22:25