Change the perspective of data in excel
I have the following scenario:
I have records of students in a table.
Each student has: Name, Email, Gender, Id, Country
Additionally, each student has Choice1, Choice1Feature1, Choice1Feature2
Choice2, Choice2Feature2
All this information is contained in a single row.
What I want to do is to transform the data such as I get the footprint to be like this:
Name, Email, Gender, Id, Country, Choice1, Choice1Feature1,ChoiceFeature2
Name, Email, Gender, Id, Country, Choice2, Choice2Feature1,Choice2Feature2.
That is, for each field in choices, where the student has completed (Choice, ChoiceFeature1, ChoiceFeature2), I would like to replicate the student information and add the ChoiceN.
I am doing this because I want to send the document and I want to ensure that people who see the doc will be able to filter the data in one column, rather than N (because of N choices) times.
Note: The case I am describing is oversimplified and the actual student has up to 25 choices and there are more than 1250 students. Thus, manipulating the data manually is not an option.
Note2: Sometimes there are 25 filled fields in the student's choices, sometimes there is only 1, because the student only set one choice.
Here is a screenshot that explains exactly what I want to achieve.
microsoft-excel microsoft-office database query
edited May 15 '18 at 15:14
Eric F
2,74531131
asked May 15 '18 at 14:44
bemihai22
12
add a comment |
I have the following scenario:
I have records of students in a table.
Each student has: Name, Email, Gender, Id, Country
Additionally, each student has Choice1, Choice1Feature1, Choice1Feature2
Choice2, Choice2Feature2
All this information is contained in a single row.
What I want to do is to transform the data such as I get the footprint to be like this:
Name, Email, Gender, Id, Country, Choice1, Choice1Feature1,ChoiceFeature2
Name, Email, Gender, Id, Country, Choice2, Choice2Feature1,Choice2Feature2.
That is, for each field in choices, where the student has completed (Choice, ChoiceFeature1, ChoiceFeature2), I would like to replicate the student information and add the ChoiceN.
I am doing this because I want to send the document and I want to ensure that people who see the doc will be able to filter the data in one column, rather than N (because of N choices) times.
Note: The case I am describing is oversimplified and the actual student has up to 25 choices and there are more than 1250 students. Thus, manipulating the data manually is not an option.
Note2: Sometimes there are 25 filled fields in the student's choices, sometimes there is only 1, because the student only set one choice.
Here is a screenshot that explains exactly what I want to achieve.
microsoft-excel microsoft-office database query
edited May 15 '18 at 15:14
Eric F
2,74531131
asked May 15 '18 at 14:44
bemihai22
12
Please add screenshots. From what I gathered you just want to add another choice?
– Eric F
May 15 '18 at 14:46
No. I would like to enlist all the (Choice, ChoiceFeature2, ChoiceFeature2) of a student and also keeping student's info.
– bemihai22
May 15 '18 at 14:50
Please add screenshots
– Eric F
May 15 '18 at 14:50
I have knowledge of PSQL and MySQL, but never written a Query for Excel. I believe that would be possible, but I wanted to see if there is a more straightforward way to do it.
– bemihai22
May 15 '18 at 15:00
My simple suggestion is, if you can please create Database in ACCESS and then Export to Excel, this will be much easier and quite helpful too, since you have many choices to Transform.
– Rajesh S
May 16 '18 at 10:41
add a comment |
I have the following scenario:
I have records of students in a table.
Each student has: Name, Email, Gender, Id, Country
Additionally, each student has Choice1, Choice1Feature1, Choice1Feature2
Choice2, Choice2Feature2
All this information is contained in a single row.
What I want to do is to transform the data such as I get the footprint to be like this:
Name, Email, Gender, Id, Country, Choice1, Choice1Feature1,ChoiceFeature2
Name, Email, Gender, Id, Country, Choice2, Choice2Feature1,Choice2Feature2.
That is, for each field in choices, where the student has completed (Choice, ChoiceFeature1, ChoiceFeature2), I would like to replicate the student information and add the ChoiceN.
I am doing this because I want to send the document and I want to ensure that people who see the doc will be able to filter the data in one column, rather than N (because of N choices) times.
Note: The case I am describing is oversimplified and the actual student has up to 25 choices and there are more than 1250 students. Thus, manipulating the data manually is not an option.
Note2: Sometimes there are 25 filled fields in the student's choices, sometimes there is only 1, because the student only set one choice.
Here is a screenshot that explains exactly what I want to achieve.
microsoft-excel microsoft-office database query
edited May 15 '18 at 15:14
Eric F
2,74531131
asked May 15 '18 at 14:44
bemihai22
12
I have the following scenario:
I have records of students in a table.
Each student has: Name, Email, Gender, Id, Country
Additionally, each student has Choice1, Choice1Feature1, Choice1Feature2
Choice2, Choice2Feature2
All this information is contained in a single row.
What I want to do is to transform the data such as I get the footprint to be like this:
Name, Email, Gender, Id, Country, Choice1, Choice1Feature1,ChoiceFeature2
Name, Email, Gender, Id, Country, Choice2, Choice2Feature1,Choice2Feature2.
That is, for each field in choices, where the student has completed (Choice, ChoiceFeature1, ChoiceFeature2), I would like to replicate the student information and add the ChoiceN.
I am doing this because I want to send the document and I want to ensure that people who see the doc will be able to filter the data in one column, rather than N (because of N choices) times.
Note: The case I am describing is oversimplified and the actual student has up to 25 choices and there are more than 1250 students. Thus, manipulating the data manually is not an option.
Note2: Sometimes there are 25 filled fields in the student's choices, sometimes there is only 1, because the student only set one choice.
Here is a screenshot that explains exactly what I want to achieve.
microsoft-excel microsoft-office database query
microsoft-excel microsoft-office database query
edited May 15 '18 at 15:14
Eric F
2,74531131
asked May 15 '18 at 14:44
bemihai22
12
edited May 15 '18 at 15:14
Eric F
2,74531131
asked May 15 '18 at 14:44
bemihai22
12
edited May 15 '18 at 15:14
Eric F
2,74531131
edited May 15 '18 at 15:14
Eric F
2,74531131
edited May 15 '18 at 15:14
Eric F
2,74531131
2,74531131
asked May 15 '18 at 14:44
bemihai22
12
asked May 15 '18 at 14:44
bemihai22
12
asked May 15 '18 at 14:44
bemihai22
12
12
Please add screenshots. From what I gathered you just want to add another choice?
– Eric F
May 15 '18 at 14:46
No. I would like to enlist all the (Choice, ChoiceFeature2, ChoiceFeature2) of a student and also keeping student's info.
– bemihai22
May 15 '18 at 14:50
Please add screenshots
– Eric F
May 15 '18 at 14:50
I have knowledge of PSQL and MySQL, but never written a Query for Excel. I believe that would be possible, but I wanted to see if there is a more straightforward way to do it.
– bemihai22
May 15 '18 at 15:00
My simple suggestion is, if you can please create Database in ACCESS and then Export to Excel, this will be much easier and quite helpful too, since you have many choices to Transform.
– Rajesh S
May 16 '18 at 10:41
add a comment |
Please add screenshots. From what I gathered you just want to add another choice?
– Eric F
May 15 '18 at 14:46
No. I would like to enlist all the (Choice, ChoiceFeature2, ChoiceFeature2) of a student and also keeping student's info.
– bemihai22
May 15 '18 at 14:50
Please add screenshots
– Eric F
May 15 '18 at 14:50
I have knowledge of PSQL and MySQL, but never written a Query for Excel. I believe that would be possible, but I wanted to see if there is a more straightforward way to do it.
– bemihai22
May 15 '18 at 15:00
My simple suggestion is, if you can please create Database in ACCESS and then Export to Excel, this will be much easier and quite helpful too, since you have many choices to Transform.
– Rajesh S
May 16 '18 at 10:41
Please add screenshots. From what I gathered you just want to add another choice?
– Eric F
May 15 '18 at 14:46
Please add screenshots. From what I gathered you just want to add another choice?
– Eric F
May 15 '18 at 14:46
No. I would like to enlist all the (Choice, ChoiceFeature2, ChoiceFeature2) of a student and also keeping student's info.
– bemihai22
May 15 '18 at 14:50
No. I would like to enlist all the (Choice, ChoiceFeature2, ChoiceFeature2) of a student and also keeping student's info.
– bemihai22
May 15 '18 at 14:50
Please add screenshots
– Eric F
May 15 '18 at 14:50
Please add screenshots
– Eric F
May 15 '18 at 14:50
I have knowledge of PSQL and MySQL, but never written a Query for Excel. I believe that would be possible, but I wanted to see if there is a more straightforward way to do it.
– bemihai22
May 15 '18 at 15:00
I have knowledge of PSQL and MySQL, but never written a Query for Excel. I believe that would be possible, but I wanted to see if there is a more straightforward way to do it.
– bemihai22
May 15 '18 at 15:00
My simple suggestion is, if you can please create Database in ACCESS and then Export to Excel, this will be much easier and quite helpful too, since you have many choices to Transform.
– Rajesh S
May 16 '18 at 10:41
My simple suggestion is, if you can please create Database in ACCESS and then Export to Excel, this will be much easier and quite helpful too, since you have many choices to Transform.
– Rajesh S
May 16 '18 at 10:41
add a comment |
1 Answer
1
active
oldest
votes
I know this post has been stale for a lot of time, but I have figured out a solution in the past and now I returned to Stack Exchange after a long period of inactivity.
Microsoft Excel (2003 onwards) allows you to query data with its integrated app called Microsoft Query.
To access Microsoft Query please go to: Data > GetExternalData > CreateNewQuery > Select "ExcelFiles" > OK.
Once you got here, you can now use SQL queries to filter/join your data with SELECT/UNION etc
answered Dec 18 '18 at 9:11
bemihai22
12
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1322905%2fchange-the-perspective-of-data-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I know this post has been stale for a lot of time, but I have figured out a solution in the past and now I returned to Stack Exchange after a long period of inactivity.
Microsoft Excel (2003 onwards) allows you to query data with its integrated app called Microsoft Query.
To access Microsoft Query please go to: Data > GetExternalData > CreateNewQuery > Select "ExcelFiles" > OK.
Once you got here, you can now use SQL queries to filter/join your data with SELECT/UNION etc
answered Dec 18 '18 at 9:11
bemihai22
12
add a comment |
I know this post has been stale for a lot of time, but I have figured out a solution in the past and now I returned to Stack Exchange after a long period of inactivity.
Microsoft Excel (2003 onwards) allows you to query data with its integrated app called Microsoft Query.
To access Microsoft Query please go to: Data > GetExternalData > CreateNewQuery > Select "ExcelFiles" > OK.
Once you got here, you can now use SQL queries to filter/join your data with SELECT/UNION etc
answered Dec 18 '18 at 9:11
bemihai22
12
add a comment |
I know this post has been stale for a lot of time, but I have figured out a solution in the past and now I returned to Stack Exchange after a long period of inactivity.
Microsoft Excel (2003 onwards) allows you to query data with its integrated app called Microsoft Query.
To access Microsoft Query please go to: Data > GetExternalData > CreateNewQuery > Select "ExcelFiles" > OK.
Once you got here, you can now use SQL queries to filter/join your data with SELECT/UNION etc
answered Dec 18 '18 at 9:11
bemihai22
12
I know this post has been stale for a lot of time, but I have figured out a solution in the past and now I returned to Stack Exchange after a long period of inactivity.
Microsoft Excel (2003 onwards) allows you to query data with its integrated app called Microsoft Query.
To access Microsoft Query please go to: Data > GetExternalData > CreateNewQuery > Select "ExcelFiles" > OK.
Once you got here, you can now use SQL queries to filter/join your data with SELECT/UNION etc
answered Dec 18 '18 at 9:11
bemihai22
12
answered Dec 18 '18 at 9:11
bemihai22
12
answered Dec 18 '18 at 9:11
bemihai22
12
answered Dec 18 '18 at 9:11
bemihai22
12
12
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1322905%2fchange-the-perspective-of-data-in-excel%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
Please add screenshots. From what I gathered you just want to add another choice?
– Eric F
May 15 '18 at 14:46
No. I would like to enlist all the (Choice, ChoiceFeature2, ChoiceFeature2) of a student and also keeping student's info.
– bemihai22
May 15 '18 at 14:50
Please add screenshots
– Eric F
May 15 '18 at 14:50
I have knowledge of PSQL and MySQL, but never written a Query for Excel. I believe that would be possible, but I wanted to see if there is a more straightforward way to do it.
– bemihai22
May 15 '18 at 15:00
My simple suggestion is, if you can please create Database in ACCESS and then Export to Excel, this will be much easier and quite helpful too, since you have many choices to Transform.
– Rajesh S
May 16 '18 at 10:41