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
1	Month	Name	Score		Name	Month	Score
2	January	Mike	Red		Mike	January	Red
3	March	Joe	Red		Joe	March	Red
4	January	Mike	Green		Mary	February	Red
5	March	Joe	Red
6	February	Mary	Red
7	March	Adam	Red

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula E2 {=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))))} F2 {=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))))} G2 {=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))))} <thead> </thead><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>

 

[billszysz]

Hi

This is perfect job for Pivot Table.