0

I would like to return the 'Standard' (columns C4:AG4) and 'Name' (A) of those that have a 'No' in either the 'Pre' or 'Post' for each Standard (see: columns C4:AG4 on the 3-5h example).

The list of names are displayed on (A4:A28).

, image: 3-5th example. I have attempted to Transpose (from Horiz to Vert) the 'Standard' values in an Index Sheet, but I'm having trouble associated the 'Standard' for each student. For example, each student completes all 10 of the Multiple choice questions and either receives a Yes (Correct) or No (Wrong) answer.

I have provided an image example of what I'm expecting.

=UNIQUE(TRANSPOSE(QUERY('3-5th Data Entry Example'!C4:AQ4)))


Desired Output:

If Student has a 'No' in either the Pre or Post (from sheet: '3-5th Data Entry sheet', their name(s) are returned

Standard 1    Student Name 1
Standard 2    Student Name 2    
Standard 3    Student Name 3
Standard 4    Student Name 4 
Standard 5    Student Name 5

Any assistance would be greatly appreciated. Thanks

Dummy Spreadsheet

enter image description here

enter image description here

Jarvis Davis
  • 33
  • 5
  • Sure one moment – Jarvis Davis Sep 01 '23 at 07:19
  • Can you share a copy of your spreadsheet with the desired output? You are more likely to get help if you provide these rather than screenshots which will take time to replicate what you have – Logan Sep 01 '23 at 07:20
  • Here is the Dummy Spreadsheet https://docs.google.com/spreadsheets/d/1VlK07K9wp2tYOlZGR4Ow32O9FLkmSeFI7JYjVe-PfpM/edit?usp=sharing – Jarvis Davis Sep 01 '23 at 07:25
  • Thanks for letting me know! Just added it – Jarvis Davis Sep 01 '23 at 07:27
  • With your current spreadsheet format, this is the simple formula: `=QUERY('3-5th Data Entry Example'!A4:D,"Select A where C = 'No' or D = 'No'")`. You need to manually adjust the C and D for every column of the standard. – Logan Sep 01 '23 at 07:51
  • Thanks although I was attempting to have the following desired output Desired Output: If Student has a 'No' in either the Pre or Post (from sheet: '3-5th Data Entry sheet', the following is returned: 3-PS2-4 Student Name 1 3-PS2-2 Student Name 2 1-LS3-1 Student Name 1 1-LS3-1 Student Name 4 1-LS3-1 Student Name 2 – Jarvis Davis Sep 01 '23 at 08:00
  • Returning the Standard and the Name for those that meet the criteria – Jarvis Davis Sep 01 '23 at 08:02

1 Answers1

1

Here's one approach you may test out:

=index(split(tocol(if('3-5th Data Entry Example'!C8:V="No",scan(,'3-5th Data Entry Example'!C4:V4,lambda(a,c,if(c="",a,c)))&"|"&'3-5th Data Entry Example'!A8:A,),1,1),"|"))

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19