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;
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Subject
[/TD]
[/TR]
[TR]
[TD]Basic
[/TD]
[TD]Subject 1
[/TD]
[/TR]
[TR]
[TD]Basic
[/TD]
[TD]Subject 3
[/TD]
[/TR]
[TR]
[TD]Common
[/TD]
[TD]Subject 2
[/TD]
[/TR]
[TR]
[TD]Core
[/TD]
[TD]Subject 1
[/TD]
[/TR]
[TR]
[TD]Common
[/TD]
[TD]Subject 3
[/TD]
[/TR]
[TR]
[TD]Core
[/TD]
[TD]Subject 1
[/TD]
[/TR]
</tbody>[/TABLE]












I want extracted records to be:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Basic
[/TD]
[TD]Subject 1
[/TD]
[/TR]
[TR]
[TD]Basic
[/TD]
[TD]Subject 3
[/TD]
[/TR]
[TR]
[TD]Common
[/TD]
[TD]Subject 2
[/TD]
[/TR]
[TR]
[TD]Common
[/TD]
[TD]Subject 3
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you.

 

[sandy666]

with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Type[/td][td=bgcolor:#5B9BD5]Subject[/td][td][/td][td=bgcolor:#70AD47]Type[/td][td=bgcolor:#70AD47]Subject[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Basic[/td][td=bgcolor:#DDEBF7]Subject 1[/td][td][/td][td=bgcolor:#E2EFDA]Basic[/td][td=bgcolor:#E2EFDA]Subject 1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Basic[/td][td]Subject 3[/td][td][/td][td]Basic[/td][td]Subject 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Common[/td][td=bgcolor:#DDEBF7]Subject 2[/td][td][/td][td=bgcolor:#E2EFDA]Common[/td][td=bgcolor:#E2EFDA]Subject 2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Core[/td][td]Subject 1[/td][td][/td][td]Common[/td][td]Subject 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Common[/td][td=bgcolor:#DDEBF7]Subject 3[/td][td][/td][td=bgcolor:#E2EFDA]Core[/td][td=bgcolor:#E2EFDA]Subject 1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Core[/td][td]Subject 1[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Type[/td][td=bgcolor:#5B9BD5]Subject[/td][td][/td][td=bgcolor:#70AD47]Type[/td][td=bgcolor:#70AD47]Subject[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Basic[/td][td=bgcolor:#DDEBF7]Subject 1[/td][td][/td][td=bgcolor:#E2EFDA]Basic[/td][td=bgcolor:#E2EFDA]Subject 1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Basic[/td][td]Subject 3[/td][td][/td][td]Basic[/td][td]Subject 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Common[/td][td=bgcolor:#DDEBF7]Subject 2[/td][td][/td][td=bgcolor:#E2EFDA]Common[/td][td=bgcolor:#E2EFDA]Subject 2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Core[/td][td]Subject 1[/td][td][/td][td]Common[/td][td]Subject 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Common[/td][td=bgcolor:#DDEBF7]Subject 3[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Core[/td][td]Subject 1[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[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))),"")