If-Statement in Google Sheets2019 Community Moderator Electiontext filter functions on Data google sheetsHow to name sheets based on names typed in cells on summary sheet?i need to calculate a time difference in google sheetsArrayFormula Index Match not working in Google Sheets but working in ExcelCombining all data into a new sheetCan't run filter on query results in google sheetsGoogle Sheets print user and timestamp in cells on download (without opening)Populate cell based on content of adjacent cellCombining multiple Google sheets into single Google spreadsheetThree Google Sheets' data graphs (pie charts) in one graph
Is there a term for accumulated dirt on the outside of your hands and feet?
World War I as a war of liberals against authoritarians?
What is the term when voters “dishonestly” choose something that they do not want to choose?
Does .bashrc contain syntax errors?
PTIJ: Do Irish Jews have "the luck of the Irish"?
Writing in a Christian voice
Comment Box for Substitution Method of Integrals
Variable completely messes up echoed string
Why are there no stars visible in cislunar space?
PTIJ What is the inyan of the Konami code in Uncle Moishy's song?
Is there a creature that is resistant or immune to non-magical damage other than bludgeoning, slashing, and piercing?
I got the following comment from a reputed math journal. What does it mean?
Can a wizard cast a spell during their first turn of combat if they initiated combat by releasing a readied spell?
What is the significance behind "40 days" that often appears in the Bible?
Knife as defense against stray dogs
Is it insecure to send a password in a `curl` command?
Print last inputted byte
What does Jesus mean regarding "Raca," and "you fool?" - is he contrasting them?
Should I be concerned about student access to a test bank?
Synchronized implementation of a bank account in Java
How does 取材で訪れた integrate into this sentence?
Generic TVP tradeoffs?
Do US professors/group leaders only get a salary, but no group budget?
In Aliens, how many people were on LV-426 before the Marines arrived?
If-Statement in Google Sheets
2019 Community Moderator Electiontext filter functions on Data google sheetsHow to name sheets based on names typed in cells on summary sheet?i need to calculate a time difference in google sheetsArrayFormula Index Match not working in Google Sheets but working in ExcelCombining all data into a new sheetCan't run filter on query results in google sheetsGoogle Sheets print user and timestamp in cells on download (without opening)Populate cell based on content of adjacent cellCombining multiple Google sheets into single Google spreadsheetThree Google Sheets' data graphs (pie charts) in one graph
I have a Google Sheet, linked below, with a column for student names and then columns for all subjects with the student marks beneath them.
The students do not all do the same subjects though.
What I would like to do is to capture the subjects and marks which each student does in the same sheet.
So that I had Student name, then Subject 1, subject 2, subject 3 etc. I only want to know the subjects that the student takes. I have created the columns for this information in cells X to AR in my sheet.
I have no idea how to do this. Please help, please...
https://docs.google.com/spreadsheets/d/1WfmUEcvFMiw-R_TMrh30jG8gLhCDT-h-dz_PP_sNn4Q/edit?usp=sharing
if-statement google-sheets google-sheets-formula
asked Mar 7 at 17:22
Robert HenningRobert Henning
83
add a comment |
I have a Google Sheet, linked below, with a column for student names and then columns for all subjects with the student marks beneath them.
The students do not all do the same subjects though.
What I would like to do is to capture the subjects and marks which each student does in the same sheet.
So that I had Student name, then Subject 1, subject 2, subject 3 etc. I only want to know the subjects that the student takes. I have created the columns for this information in cells X to AR in my sheet.
I have no idea how to do this. Please help, please...
https://docs.google.com/spreadsheets/d/1WfmUEcvFMiw-R_TMrh30jG8gLhCDT-h-dz_PP_sNn4Q/edit?usp=sharing
if-statement google-sheets google-sheets-formula
asked Mar 7 at 17:22
Robert HenningRobert Henning
83
A list all in one cell or in separate cells?
– Emily Alden
Mar 7 at 17:31
@EmilyAlden Thanks for your reply, I want to populate the cells X to AR in my sheet with the subject information (subject name, subject code, subject mark) for each learner.
– Robert Henning
Mar 8 at 6:44
add a comment |
I have a Google Sheet, linked below, with a column for student names and then columns for all subjects with the student marks beneath them.
The students do not all do the same subjects though.
What I would like to do is to capture the subjects and marks which each student does in the same sheet.
So that I had Student name, then Subject 1, subject 2, subject 3 etc. I only want to know the subjects that the student takes. I have created the columns for this information in cells X to AR in my sheet.
I have no idea how to do this. Please help, please...
https://docs.google.com/spreadsheets/d/1WfmUEcvFMiw-R_TMrh30jG8gLhCDT-h-dz_PP_sNn4Q/edit?usp=sharing
if-statement google-sheets google-sheets-formula
asked Mar 7 at 17:22
Robert HenningRobert Henning
83
I have a Google Sheet, linked below, with a column for student names and then columns for all subjects with the student marks beneath them.
The students do not all do the same subjects though.
What I would like to do is to capture the subjects and marks which each student does in the same sheet.
So that I had Student name, then Subject 1, subject 2, subject 3 etc. I only want to know the subjects that the student takes. I have created the columns for this information in cells X to AR in my sheet.
I have no idea how to do this. Please help, please...
https://docs.google.com/spreadsheets/d/1WfmUEcvFMiw-R_TMrh30jG8gLhCDT-h-dz_PP_sNn4Q/edit?usp=sharing
if-statement google-sheets google-sheets-formula
if-statement google-sheets google-sheets-formula
asked Mar 7 at 17:22
Robert HenningRobert Henning
83
asked Mar 7 at 17:22
Robert HenningRobert Henning
83
edited Mar 8 at 7:13
Robert Henning
asked Mar 7 at 17:22
Robert HenningRobert Henning
83
asked Mar 7 at 17:22
Robert HenningRobert Henning
83
asked Mar 7 at 17:22
Robert HenningRobert Henning
83
83
A list all in one cell or in separate cells?
– Emily Alden
Mar 7 at 17:31
@EmilyAlden Thanks for your reply, I want to populate the cells X to AR in my sheet with the subject information (subject name, subject code, subject mark) for each learner.
– Robert Henning
Mar 8 at 6:44
add a comment |
A list all in one cell or in separate cells?
– Emily Alden
Mar 7 at 17:31
@EmilyAlden Thanks for your reply, I want to populate the cells X to AR in my sheet with the subject information (subject name, subject code, subject mark) for each learner.
– Robert Henning
Mar 8 at 6:44
A list all in one cell or in separate cells?
– Emily Alden
Mar 7 at 17:31
A list all in one cell or in separate cells?
– Emily Alden
Mar 7 at 17:31
@EmilyAlden Thanks for your reply, I want to populate the cells X to AR in my sheet with the subject information (subject name, subject code, subject mark) for each learner.
– Robert Henning
Mar 8 at 6:44
@EmilyAlden Thanks for your reply, I want to populate the cells X to AR in my sheet with the subject information (subject name, subject code, subject mark) for each learner.
– Robert Henning
Mar 8 at 6:44
add a comment |
2 Answers
2
active
oldest
votes
=ARRAYFORMULA(SPLIT(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(
ARRAYFORMULA(IF(E3:W8<>"", $E$1:$W$1, ))&","), , 40000)), " ,", ""),","))
answered Mar 7 at 18:13
player0player0
5,51841232
Thank you @player0 this is close to what I am looking for, however, I realize now that I should clarify. I want to extract the subjects which the students do as well as the (S) code for each subject and the mark which they got for the subject. I created the columns for this on the far right of the sheet (Cells X to AR). Is it possible to populate those cells with the data?
– Robert Henning
Mar 8 at 6:26
not sure I understand "code for each subject" part...
– player0
Mar 8 at 18:16
Sorry ignore that, I meant mark for each subject.
– Robert Henning
Mar 11 at 8:29
1
Sorry, @player0 my mistake - your answer if perfect and you are a Sheets Genius.
– Robert Henning
Mar 11 at 11:31
add a comment |
If you are okay with it being a list in a single cell you can use the following logic:
=IF(E3<>0,E$1 & ʺ, ʺ,ʺʺ) & IF(F3<>0,F$1 & ʺ, ʺ,ʺʺ) & ʺ...etcʺ
Just add to it with G,H,etc following the same pattern.
If you want to fill in the table on the back end of your calculation it will take more work.
answered Mar 7 at 17:41
Emily AldenEmily Alden
407213
I want to fill in the table to the right of my data in cells X to AR.
– Robert Henning
Mar 8 at 7:14
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%2f55049603%2fif-statement-in-google-sheets%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
=ARRAYFORMULA(SPLIT(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(
ARRAYFORMULA(IF(E3:W8<>"", $E$1:$W$1, ))&","), , 40000)), " ,", ""),","))
answered Mar 7 at 18:13
player0player0
5,51841232
Thank you @player0 this is close to what I am looking for, however, I realize now that I should clarify. I want to extract the subjects which the students do as well as the (S) code for each subject and the mark which they got for the subject. I created the columns for this on the far right of the sheet (Cells X to AR). Is it possible to populate those cells with the data?
– Robert Henning
Mar 8 at 6:26
not sure I understand "code for each subject" part...
– player0
Mar 8 at 18:16
Sorry ignore that, I meant mark for each subject.
– Robert Henning
Mar 11 at 8:29
1
Sorry, @player0 my mistake - your answer if perfect and you are a Sheets Genius.
– Robert Henning
Mar 11 at 11:31
add a comment |
=ARRAYFORMULA(SPLIT(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(
ARRAYFORMULA(IF(E3:W8<>"", $E$1:$W$1, ))&","), , 40000)), " ,", ""),","))
answered Mar 7 at 18:13
player0player0
5,51841232
Thank you @player0 this is close to what I am looking for, however, I realize now that I should clarify. I want to extract the subjects which the students do as well as the (S) code for each subject and the mark which they got for the subject. I created the columns for this on the far right of the sheet (Cells X to AR). Is it possible to populate those cells with the data?
– Robert Henning
Mar 8 at 6:26
not sure I understand "code for each subject" part...
– player0
Mar 8 at 18:16
Sorry ignore that, I meant mark for each subject.
– Robert Henning
Mar 11 at 8:29
1
Sorry, @player0 my mistake - your answer if perfect and you are a Sheets Genius.
– Robert Henning
Mar 11 at 11:31
add a comment |
=ARRAYFORMULA(SPLIT(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(
ARRAYFORMULA(IF(E3:W8<>"", $E$1:$W$1, ))&","), , 40000)), " ,", ""),","))
answered Mar 7 at 18:13
player0player0
5,51841232
=ARRAYFORMULA(SPLIT(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(
ARRAYFORMULA(IF(E3:W8<>"", $E$1:$W$1, ))&","), , 40000)), " ,", ""),","))
answered Mar 7 at 18:13
player0player0
5,51841232
answered Mar 7 at 18:13
player0player0
5,51841232
answered Mar 7 at 18:13
player0player0
5,51841232
answered Mar 7 at 18:13
player0player0
5,51841232
5,51841232
Thank you @player0 this is close to what I am looking for, however, I realize now that I should clarify. I want to extract the subjects which the students do as well as the (S) code for each subject and the mark which they got for the subject. I created the columns for this on the far right of the sheet (Cells X to AR). Is it possible to populate those cells with the data?
– Robert Henning
Mar 8 at 6:26
not sure I understand "code for each subject" part...
– player0
Mar 8 at 18:16
Sorry ignore that, I meant mark for each subject.
– Robert Henning
Mar 11 at 8:29
1
Sorry, @player0 my mistake - your answer if perfect and you are a Sheets Genius.
– Robert Henning
Mar 11 at 11:31
add a comment |
Thank you @player0 this is close to what I am looking for, however, I realize now that I should clarify. I want to extract the subjects which the students do as well as the (S) code for each subject and the mark which they got for the subject. I created the columns for this on the far right of the sheet (Cells X to AR). Is it possible to populate those cells with the data?
– Robert Henning
Mar 8 at 6:26
not sure I understand "code for each subject" part...
– player0
Mar 8 at 18:16
Sorry ignore that, I meant mark for each subject.
– Robert Henning
Mar 11 at 8:29
1
Sorry, @player0 my mistake - your answer if perfect and you are a Sheets Genius.
– Robert Henning
Mar 11 at 11:31
Thank you @player0 this is close to what I am looking for, however, I realize now that I should clarify. I want to extract the subjects which the students do as well as the (S) code for each subject and the mark which they got for the subject. I created the columns for this on the far right of the sheet (Cells X to AR). Is it possible to populate those cells with the data?
– Robert Henning
Mar 8 at 6:26
Thank you @player0 this is close to what I am looking for, however, I realize now that I should clarify. I want to extract the subjects which the students do as well as the (S) code for each subject and the mark which they got for the subject. I created the columns for this on the far right of the sheet (Cells X to AR). Is it possible to populate those cells with the data?
– Robert Henning
Mar 8 at 6:26
not sure I understand "code for each subject" part...
– player0
Mar 8 at 18:16
not sure I understand "code for each subject" part...
– player0
Mar 8 at 18:16
Sorry ignore that, I meant mark for each subject.
– Robert Henning
Mar 11 at 8:29
Sorry ignore that, I meant mark for each subject.
– Robert Henning
Mar 11 at 8:29
1
1
Sorry, @player0 my mistake - your answer if perfect and you are a Sheets Genius.
– Robert Henning
Mar 11 at 11:31
Sorry, @player0 my mistake - your answer if perfect and you are a Sheets Genius.
– Robert Henning
Mar 11 at 11:31
add a comment |
If you are okay with it being a list in a single cell you can use the following logic:
=IF(E3<>0,E$1 & ʺ, ʺ,ʺʺ) & IF(F3<>0,F$1 & ʺ, ʺ,ʺʺ) & ʺ...etcʺ
Just add to it with G,H,etc following the same pattern.
If you want to fill in the table on the back end of your calculation it will take more work.
answered Mar 7 at 17:41
Emily AldenEmily Alden
407213
I want to fill in the table to the right of my data in cells X to AR.
– Robert Henning
Mar 8 at 7:14
add a comment |
If you are okay with it being a list in a single cell you can use the following logic:
=IF(E3<>0,E$1 & ʺ, ʺ,ʺʺ) & IF(F3<>0,F$1 & ʺ, ʺ,ʺʺ) & ʺ...etcʺ
Just add to it with G,H,etc following the same pattern.
If you want to fill in the table on the back end of your calculation it will take more work.
answered Mar 7 at 17:41
Emily AldenEmily Alden
407213
I want to fill in the table to the right of my data in cells X to AR.
– Robert Henning
Mar 8 at 7:14
add a comment |
If you are okay with it being a list in a single cell you can use the following logic:
=IF(E3<>0,E$1 & ʺ, ʺ,ʺʺ) & IF(F3<>0,F$1 & ʺ, ʺ,ʺʺ) & ʺ...etcʺ
Just add to it with G,H,etc following the same pattern.
If you want to fill in the table on the back end of your calculation it will take more work.
answered Mar 7 at 17:41
Emily AldenEmily Alden
407213
If you are okay with it being a list in a single cell you can use the following logic:
=IF(E3<>0,E$1 & ʺ, ʺ,ʺʺ) & IF(F3<>0,F$1 & ʺ, ʺ,ʺʺ) & ʺ...etcʺ
Just add to it with G,H,etc following the same pattern.
If you want to fill in the table on the back end of your calculation it will take more work.
answered Mar 7 at 17:41
Emily AldenEmily Alden
407213
answered Mar 7 at 17:41
Emily AldenEmily Alden
407213
answered Mar 7 at 17:41
Emily AldenEmily Alden
407213
answered Mar 7 at 17:41
Emily AldenEmily Alden
407213
407213
I want to fill in the table to the right of my data in cells X to AR.
– Robert Henning
Mar 8 at 7:14
add a comment |
I want to fill in the table to the right of my data in cells X to AR.
– Robert Henning
Mar 8 at 7:14
I want to fill in the table to the right of my data in cells X to AR.
– Robert Henning
Mar 8 at 7:14
I want to fill in the table to the right of my data in cells X to AR.
– Robert Henning
Mar 8 at 7:14
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.
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%2f55049603%2fif-statement-in-google-sheets%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
A list all in one cell or in separate cells?
– Emily Alden
Mar 7 at 17:31
@EmilyAlden Thanks for your reply, I want to populate the cells X to AR in my sheet with the subject information (subject name, subject code, subject mark) for each learner.
– Robert Henning
Mar 8 at 6:44