0

I have the following data:

Sample Data

and would like to create a separate sheet that would Identify the First name, Last name and the header of columns E:N if they have a value of 1.

I would like to have the separate sheet include a dropdown list of names in column A1 to populate the data.

1

UNTESTED (what, with that much data and in a barely legible image ?!)

Copy the entire populated part of your spreadsheet below existing data (say to A100) and fill the central area (starting E101) with:

 =if(E2=1,E$1,"")

Count row contents with:

=countunique(E101:N101)

in O101 and fill down to suit.

In your other sheet:

 =FILTER({Sheet!A101:B201,Sheet!E101:S201},Sheet!O101:O201>0)
  • sorry here's the sample sheet: drive.google.com/… – Eddie Aug 3 '17 at 18:22
  • I changed : =FILTER({Sheet!A101:B201,Sheet!E101:S201},Sheet!O101:O201>0) to =FILTER({Sheet1!B101:C201,Sheet1!E101:FP201},Sheet1!FQ101:FQ201>0) Because this is the whole table – Eddie Aug 3 '17 at 18:37
  • I would like to be able to pull data for one teacher (first name and last name name) Column B:C and the column headers that have a value. – Eddie Aug 3 '17 at 18:39
  • This is new to me not sure what the transpose process would look like. – Eddie Aug 3 '17 at 19:21
  • I can use the following formula to give me the first column name that contains a value using: =ArrayFormula(INDEX($E$1:$FP$1,MATCH(FALSE,ISBLANK(E2:FP2),0))) – Eddie Aug 3 '17 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.