Filtering data with VLOOKUP?

[vblehtonen]

Hey,

I have a Learning Management System (LMS) online and I can get the excel-reports from online who has done specific courses. I would like to see who has done all the trainings I get the report from so the file looks like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Col. A[/TD]
[TD]Col. B[/TD]
[TD]Col. C[/TD]
[TD]Col. D[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]Training A[/TD]
[TD]Bob[/TD]
[TD]Saget[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]Training A[/TD]
[TD]Bob[/TD]
[TD]Saget[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]Training B[/TD]
[TD]Christie[/TD]
[TD]Dump[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]Training B[/TD]
[TD]Matt[/TD]
[TD]Stevens[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]Training A[/TD]
[TD]Matt[/TD]
[TD]Stevens[/TD]
[/TR]
</tbody>[/TABLE]


Col. A = Has person finished the training (YES/NO)
Col. B = Name of the training (Training A, Training B)
Col. C = First Name of person
Col. D = Surname of person

So the problem is, that there's at least duplicates (the same person has done Training A, Training B), and I would like to only get the names of the persons who have done both trainings, in this case only would like to get "Matt Stevens" as the result. But there will be multiple persons who have done both trainings.

Any ideas? Thank you already for your help wizards!

 

[VBA Geek]

with a formula you can do it like this


Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
people who have completed both training
NAME	LAST NAME
Matt	Stevens
Bob	Saget

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]yes/no[/TD]
[TD="align: center"]trainingName[/TD]
[TD="align: center"]firstName[/TD]
[TD="align: center"]lastName[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]unique trainigs:[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]YES[/TD]
[TD="align: center"]Training A[/TD]
[TD="align: center"]Bob[/TD]
[TD="align: center"]Saget[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]3[/TD]
[TD="align: center"]YES[/TD]
[TD="align: center"]Training B
[/TD]
[TD="align: center"]Bob[/TD]
[TD="align: center"]Saget[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]4[/TD]
[TD="align: center"]YES[/TD]
[TD="align: center"]Training B[/TD]
[TD="align: center"]Christie[/TD]
[TD="align: center"]Dump[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]5[/TD]
[TD="align: center"]YES[/TD]
[TD="align: center"]Training B[/TD]
[TD="align: center"]Matt[/TD]
[TD="align: center"]Stevens[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]6[/TD]
[TD="align: center"]YES[/TD]
[TD="align: center"]Training A[/TD]
[TD="align: center"]Matt[/TD]
[TD="align: center"]Stevens[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

</tbody>

Sheet11​

Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I1[/TH]
[TD="align: left"]{=SUM(1/COUNTIF(B2:B6,B2:B6))}[/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


Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]{=SUM((COUNTIFS(C2:C6,C2:C6,D2:D6,D2:D6,A2:A6,"YES")=$I$1)/$I$1)}[/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



Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]{=IF(ROWS(I$4:I4)>$I$2,"",INDEX(C$2:C$6,LARGE(IF(COUNTIFS($C$2:$C$6,$C$2:$C$6,$D$2:$D$6,$D$2:$D$6,$A$2:$A$6,"YES")=$I$1,MATCH($C$2:$C$6&$D$2:$D$6,$C$2:$C$6&$D$2:$D$6,0)),1+SUM(COUNTIFS($C$2:$C$6,$I$3:$I3,$D$2:$D$6,$J$3:$J3)))))}[/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


Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]{=IF(ROWS(J$4:J4)>$I$2,"",INDEX(D$2:D$6,LARGE(IF(COUNTIFS($C$2:$C$6,$C$2:$C$6,$D$2:$D$6,$D$2:$D$6,$A$2:$A$6,"YES")=$I$1,MATCH($C$2:$C$6&$D$2:$D$6,$C$2:$C$6&$D$2:$D$6,0)),1+SUM(COUNTIFS($C$2:$C$6,$I$3:$I3,$D$2:$D$6,$J$3:$J3)))))}[/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