Extract records based on two criteria

[basic101]

Hello! im just newbie in excel my question is what formula do I need to get just subjects of all Basic and Common type.
My sample dataset;

Type	Subject
Basic	Subject 1
Basic	Subject 3
Common	Subject 2
Core	Subject 1
Common	Subject 3
Core	Subject 1

<tbody>
</tbody>

I want extracted records to be:

Basic	Subject 1
Basic	Subject 3
Common	Subject 2
Common	Subject 3

<tbody>
</tbody>

Thank you.

 

[sandy666]

with PowerQuery (Get&Transform)

Type	Subject		Type	Subject
Basic	Subject 1		Basic	Subject 1
Basic	Subject 3		Basic	Subject 3
Common	Subject 2		Common	Subject 2
Core	Subject 1		Common	Subject 3
Common	Subject 3		Core	Subject 1
Core	Subject 1

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Type"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Subject", each List.Distinct(Table.Column([Count],"Subject"))),
    #"Expanded Subject" = Table.ExpandListColumn(#"Added Custom", "Subject")
in
    #"Expanded Subject"[/SIZE]

in a meantime you can filter Core

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Type"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Subject", each List.Distinct(Table.Column([Count],"Subject"))),
    #"Expanded Subject" = Table.ExpandListColumn(#"Added Custom", "Subject"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Subject", each ([Type] <> "Core"))
in
    #"Filtered Rows"[/SIZE]

Type	Subject		Type	Subject
Basic	Subject 1		Basic	Subject 1
Basic	Subject 3		Basic	Subject 3
Common	Subject 2		Common	Subject 2
Core	Subject 1		Common	Subject 3
Common	Subject 3
Core	Subject 1

 

[DanteAmor]

Welcome to the Board!

Excel Workbook
	A	B	C	D	E
1	Type	Subject		Type	Subject
2	Basic	Subject 1		Basic	Subject 1
3	Basic	Subject 3		Basic	Subject 3
4	Common	Subject 2		Common	Subject 2
5	Core	Subject 1		Common	Subject 3
6	Common	Subject 3
7	Core	Subject 1
8
9
10
sheet1

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[Fluff]

Or with a normal formula

Excel 2013/2016
	A	B	C	D	E
1	Type	Subject		Type	Subject
2	Basic	Subject 1		Basic	Subject 1
3	Basic	Subject 3		Basic	Subject 3
4	Common	Subject 2		Common	Subject 2
5	Core	Subject 1		Common	Subject 3
6	Common	Subject 3
7	Core	Subject 1
Template

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX(A$2:A$7,AGGREGATE(15,6,ROW($A$2:$A$7)-ROW($A$2)+1/($A$2:$A$7={"Basic","Common"}),ROWS($1:1))),"")
E2	=IFERROR(INDEX(B$2:B$7,AGGREGATE(15,6,ROW($A$2:$A$7)-ROW($A$2)+1/($A$2:$A$7={"Basic","Common"}),ROWS($1:1))),"")