Assemble conditional list and remove blanks

**GLQ0330** · 10-20-2014, 07:08 PM

StudentRequirements.xlsx

Teacher needs help.

I have a large array of student data based on required courses needed per student(see attached - worksheet 1). We will group the students according to Classrooms which will deliver similar content (e.g. Group A needs Course 1, 2, 3, n). I need to evaluate that array based on the courses marked with an "X" and build a student attendee list on Sheet 2. However, the formula needs to evaluate several conditions for example: If Sheet1 B3 is X and Sheet 1 C3 is X and if Sheet 1 D3 is X then put A3 on Sheet 2. Exclude any blanks on sheet 2.

I have played with this all day: =IF('Sheet1'!G:G="x", 'Sheet1'!A6, "") but this returns blanks and does not evaluate for multiple courses needed.

Signed Frustrated in the Classroom.

**sktneer** · 10-20-2014, 07:49 PM

Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter.
On Sheet2
In A2

Please Login or Register  to view this content.

and copy it down until you get blank cells.
Is this what you are trying to achieve?

Note: When an array formula is correctly entered, you will notice in the formula bar that the formula gets surrounded by the Curly Brackets { } and if you don't see the curly brackets, select the formula cell, press F2 (Function Key), hold down the Ctrl+Shift keys together and then hit Enter. Once formula is correctly entered, you can just hold the fill handle and drag the formula down the rows as per your need.