how to I get Excel to automate this process?

[DentonHTHS]

Hi everyone, I have a sample file of the work I am trying to do with a much larger file. Basically, I would like the second tab to automatically pull the data from the first tab. On tab 2, I will have the column of student names but would like to have the number of courses they are failing and the name of the course(s) listed out on the same line. On the first tab, the number "1" indicates that the student is taking the course and is failing.

Any help would be greatly appreciated.

DentonHTHS
Tab 1

Student	Course 1	Course 2	Course 3	Course 4	Course 5
Bruce Wayne	1		1		1
Hal Jordan	1		1	1
Barry Allen			1		1
Diana Prince		1
Clark Kent	1		1

<tbody>
</tbody>

Tab 2

Student	# Courses Failing	1	2	3	4	5
Bruce Wayne	3	Course 1	Course 3	Course 5
Hal Jordan	3	Course 1	Course 3	Course 4
Barry Allen	2	Course 3	Course 5
Diana Prince	1	Course 2
Clark Kent	2	Course 1	Course 3

<tbody>
</tbody>

I need formulas that will insert the names of the courses each student has failed in the spaces in Colums B-G and total the amount failed in Column B pulling the data from Tab 1

 

[Weazel]

assuming the data on tab1 starts in column A you could try

Excel 2013

	A	B	C	D	E	F	G
8	Student	Courses Failing	1	2	3	4	5
9	Bruce Wayne	3	Course 1	Course 3	Course 5
10	Hal Jordan	3	Course 1	Course 3	Course 4
11	Barry Allen	2	Course 3	Course 5
12	Diana Prince	1	Course 2
13	Clark Kent	2	Course 1	Course 3

<tbody>
</tbody>

tab 1​

Worksheet Formulas Cell Formula B9 =SUMPRODUCT(('tab 1'!$A$2:$A$6=A9)*('tab 1'!$B$2:$F$6)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula C9 {=IFERROR(INDEX('tab 1'!$B$1:$F$1,SMALL(IF(('tab 1'!$A$2:$A$6=$A9)*('tab 1'!$B$2:$F$6=1),COLUMN('tab 1'!$B$1:$F$1)-COLUMN('tab 1'!$B$1)+1),COLUMNS($C9:C9))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Eric W]

A similar, but slightly different version:

	A	B	C	D	E	F
1	Student	Course 1	Course 2	Course 3	Course 4	Course 5
2	Bruce Wayne	1		1		1
3	Hal Jordan	1		1	1
4	Barry Allen			1		1
5	Kara Danvers
6	Diana Prince		1
7	Clark Kent	1		1

<tbody>
</tbody>

Sheet1​

	A	B	C	D	E	F	G
1	Student	# Courses Failing	1	2	3	4	5
2	Bruce Wayne	3	Course 1	Course 3	Course 5
3	Hal Jordan	3	Course 1	Course 3	Course 4
4	Barry Allen	2	Course 3	Course 5
5	Diana Prince	1	Course 2
6	Clark Kent	2	Course 1	Course 3

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B2 =IF(A2="","",SUMPRODUCT((Sheet1!$A$2:$A$7=A2)*(Sheet1!$B$2:$F$7=1))) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A2 {=IF(A1="","",IFERROR(INDEX(Sheet1!A:A,SMALL(IF(SUBTOTAL(2,OFFSET(Sheet1!$A$1,ROW($A$2:$A$7)-ROW($A$2)+1,1,1,5))>0,ROW($A$2:$A$7)),ROWS($A$2:$A2))),""))} C2 {=IF(B2="","",IFERROR(INDEX(Sheet1!$B$1:$G$1,SMALL(IF(SUBTOTAL(2,OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A$2:$A$7,0),ROW($1:$5)))>0,ROW($1:$5)),COLUMNS($C2:C2))),""))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The formula in A2 will list only those students with at least 1 failing class. Put in A2, confirm with Control+Shift+Enter and copy down. Put B2 in and copy down. Put in C2, confirm with Control+Shift+Enter and copy down and across. These formulas do assume that on the grid on Sheet1, the accepted values are 1 or empty. If there are other possible values, I'll need to adjust the formulas.

You might also want to consider a VBA solution, since that would be fairly simple to code.

 

[DentonHTHS]

I have tried to apply this formula to my larger sheet but haven't had any luck making the changes. I either get a circular expression argument or that I can't change part of an array. The workbook I am using has 285 student names as the rows and the courses being taught as the columns (from A to BZ). If a student is failing the course there is a "1" in the cell under that course corresponding to the student, otherwise it is blank. Using the same parameters able how do I edit the formula to make it work for this larger workbook. I am missing something somewhere.

 

[Weazel]

Ok, attached is a sample workbook with the 2 tabs.

the student names are randomly generated to give you something to reference.

tab 2 has the formula you would need to extract and count the data from tab1

hopefully, that helps.

https://app.box.com/s/x6r9ps2v90psy8gp46fxcbeq3ttjx11t

 

[DentonHTHS]

Thank you! I got that to work. I am copying it down and making a note so I can do it properly the next time.
Much appreciated!