CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formula
Robert Henning
Robert Henning

Reputation: 43

If-Statement in Google Sheets

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

Upvotes: 1

Views: 64

Answers (2)

player0
player0

Reputation: 1

=ARRAYFORMULA(SPLIT(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(
 ARRAYFORMULA(IF(E3:W8<>"", $E$1:$W$1, ))&","), , 40000)), " ,", ""),","))

0

Upvotes: 1

Emily Alden
Emily Alden

Reputation: 570

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.

Upvotes: 0

Related Questions