Show a list of data based on a value in a column on another sheet

[carura]

Hi Excel masters.

I just need help with the formula to use in my report

Let's say I have a summary table on Sheet 1
6000 rows of data on sheet 2.

In Sheet 2
I have 3 columns, Name, Month and Score.
Name Column (A) has names of students
Month Column (B) has months January, February and so on.
Score Column (C) has data from Green, Yellow and Red.
Red is if they failed.

In the summary table on Sheet 1, I need to show all the names that got a "Red" Score. And their corresponding months.

It should look like this.

Month l Name l Score
-----------------------------------------
January l Mike J l Red
-----------------------------------------
March l Joe M l Red


If you have any idea please respond as soon as you can.

Thanks!

 

[Weazel]

maybe something like..

Excel 2012

	A	B	C	D	E	F	G
January	Mike	Red	Mike	January	Red
March	Joe	Red	Joe	March	Red
January	Mike	Green	Mary	February	Red
March	Joe	Red
February	Mary	Red
March	Adam	Red

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Month[/TD]
[TD="bgcolor: #FCE4D6"]Name[/TD]
[TD="bgcolor: #FCE4D6"]Score[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Name[/TD]
[TD="bgcolor: #FFFF00"]Month[/TD]
[TD="bgcolor: #FFFF00"]Score[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=IF(ROWS($E$2:$E2)>COUNTIF(Sheet2!$C$2:$C$7,"Red"),"",INDEX(Sheet2!B$2:B$7,SMALL(IF(Sheet2!$C$2:$C$7="Red",ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1),ROWS($E$2:$E2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=IF(ROWS($E$2:$E2)>COUNTIF(Sheet2!$C$2:$C$7,"Red"),"",INDEX(Sheet2!A$2:A$7,SMALL(IF(Sheet2!$C$2:$C$7="Red",ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1),ROWS($E$2:$E2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]{=IF(ROWS($E$2:$E2)>COUNTIF(Sheet2!$C$2:$C$7,"Red"),"",INDEX(Sheet2!C$2:C$7,SMALL(IF(Sheet2!$C$2:$C$7="Red",ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1),ROWS($E$2:$E2))))}[/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]

 

[billszysz]

Hi

This is perfect job for Pivot Table.