how to I get Excel to automate this process?

DentonHTHS

New Member
Joined
Jul 3, 2008
Messages
39
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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Student[/TD]
[TD]Course 1[/TD]
[TD]Course 2[/TD]
[TD]Course 3[/TD]
[TD]Course 4[/TD]
[TD]Course 5[/TD]
[/TR]
[TR]
[TD]Bruce Wayne[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Hal Jordan[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Barry Allen[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Diana Prince[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Clark Kent[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Tab 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Student[/TD]
[TD]# Courses Failing[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Bruce Wayne[/TD]
[TD]3[/TD]
[TD]Course 1[/TD]
[TD]Course 3[/TD]
[TD]Course 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hal Jordan[/TD]
[TD]3[/TD]
[TD]Course 1[/TD]
[TD]Course 3[/TD]
[TD]Course 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Barry Allen[/TD]
[TD]2[/TD]
[TD]Course 3[/TD]
[TD]Course 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Diana Prince[/TD]
[TD]1[/TD]
[TD]Course 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Clark Kent[/TD]
[TD]2[/TD]
[TD]Course 1[/TD]
[TD]Course 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
assuming the data on tab1 starts in column A you could try

Excel 2013
ABCDEFG
Courses Failing
Course 1Course 3Course 5
Course 1Course 3Course 4
Course 3Course 5
Course 2
Course 1Course 3

<tbody>
[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Student[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]Bruce Wayne[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]Hal Jordan[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]Barry Allen[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]Diana Prince[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]Clark Kent[/TD]
[TD="align: right"]2[/TD]

</tbody>
tab 1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]=SUMPRODUCT(('tab 1'!$A$2:$A$6=A9)*('tab 1'!$B$2:$F$6))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
A similar, but slightly different version:

ABCDEF

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Student[/TD]
[TD="bgcolor: #FAFAFA"]Course 1[/TD]
[TD="bgcolor: #FAFAFA"]Course 2[/TD]
[TD="bgcolor: #FAFAFA"]Course 3[/TD]
[TD="bgcolor: #FAFAFA"]Course 4[/TD]
[TD="bgcolor: #FAFAFA"]Course 5[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Bruce Wayne[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Hal Jordan[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Barry Allen[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Kara Danvers[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Diana Prince[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Clark Kent[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

</tbody>
Sheet1



ABCDEFG
Student# Courses Failing
Bruce WayneCourse 1Course 3Course 5
Hal JordanCourse 1Course 3Course 4
Barry AllenCourse 3Course 5
Diana PrinceCourse 2
Clark KentCourse 1Course 3

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2[/TD]

</tbody>
Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(A2="","",SUMPRODUCT((Sheet1!$A$2:$A$7=A2)*(Sheet1!$B$2:$F$7=1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]{=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))),""))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=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))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
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. :(
 
Last edited:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top