Pass/Fail return Scene failed w/ other info

[ExceLoki]

pretty sure i've done/seen this before, but drawing a blank on it atm, assistance is always appreciated!

first part has a table users fill out grading scenes as pass fail. i want on another page for anytime a fail happens to populate the client, id, type, and which scene was marked as fail.
------------

testing tracking.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Client	id	type		scene 1	scene 2	scene 3	scene 4	scene 5	scene 6	scene 7	scene 8
2	1	422710	LS		Pass	Pass	Pass	Pass	Pass	Fail	Pass	Pass
3	1	422710	SL		Pass	Fail	Pass	Pass	Pass	Pass	Pass	Pass
4	1	422710	JA		Pass	Pass	Pass	Pass	Pass	Pass	Pass	Fail
5	1	422710	JS		Pass	Pass	Pass	Pass	Fail	Pass	Pass	Pass
6	1	422710	CL		Pass	Pass	Pass	Pass	Pass	Pass	Pass	Pass
7	2	394285	LS		Pass	Pass	Pass	Fail	Pass	Pass	Pass	Pass
8	2	394285	SL		Pass	Pass	Pass	Pass	Pass	Pass	Pass	Pass
9	2	394285	JA		Pass	Pass	Pass	Pass	Pass	Fail	Fail	Pass
10	2	394285	JS		Fail	Pass	Fail	Pass	Pass	Pass	Pass	Fail
11	2	394285	CL		Pass	Pass	Pass	Pass	Pass	Pass	Pass	Pass
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E10	Cell Value	="Pass"	text	NO
E10	Cell Value	="Fail"	text	NO
F3	Cell Value	="Pass"	text	NO
F3	Cell Value	="Fail"	text	NO
E2:E9,F4:G9,F10,E11:L11	Cell Value	="Pass"	text	NO
E2:E9,F4:G9,F10,E11:L11	Cell Value	="Fail"	text	NO
F2	Cell Value	="Pass"	text	NO
F2	Cell Value	="Fail"	text	NO
G10,G2:L2,G3,H3:L10	Cell Value	="Pass"	text	NO
G10,G2:L2,G3,H3:L10	Cell Value	="Fail"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E2:L11	List	=validation!$C$1:$C$2

------------

testing tracking.xlsx
	A	B	C	D
1	Client	id	type	scene failed
2	1	422710	LS	scene 6
3	1	422710	SL	scene 2
4	1	422710	JA	scene 8
5	1	422710	JS	scene 5
6	2	394285	LS	scene 4
7	2	394285	JA	scene 6
8	2	394285	JA	scene 7
9	2	394285	JS	scene 1
10	2	394285	JS	scene 3
11	2	394285	JS	scene 8
Fail Notes

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	Client	id	type	scene failed
2	1	422710	LS	scene 6
3	1	422710	SL	scene 2
4	1	422710	JA	scene 8
5	1	422710	JS	scene 5
6	2	394285	LS	scene 4
7	2	394285	JA	scene 6
8	2	394285	JA	scene 7
9	2	394285	JS	scene 1
10	2	394285	JS	scene 3
11	2	394285	JS	scene 8
Sheet1

Cell Formulas
Range	Formula
A2:C11	A2	=TOCOL(IF(Sheet2!$E$2:$L$11="fail",Sheet2!A2:A11,1/0),2)
D2:D11	D2	=TOCOL(IF(Sheet2!$E$2:$L$11="fail",Sheet2!E1:L1,1/0),2)
Dynamic array formulas.

 

[ExceLoki]

thanks fluff, worked great.
my problem was trying to do it all at once, not in 2 parts.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Joe4]

If the data was structured in what is known as a "normalized database" structure, where the columns were:
Client, id, type, scene number, pass/fail value
(so you would have less columns and more rows)
soemthing like this would be pretty easy to do with the new FILTER function in Excel 365.