Match Data between 2 columns and fetch the matching data to a 3rd column

[ssinha23]

I have two sheets in the same excel, where I have names of students in column A of both sheets. I need to match which names appear in both the sheets in column A and put that on a different sheet.

For Examaple, suppose I have A, B, C ,F, K , H ,M , L, Y, Z in column A of sheet 1 and have the names of C, M, K, A, J, R, Y, E, D, Q, X in sheet 2, then these two columns should be compared, and then the matching names in both of them which are A, C, K, M, should come up on column A in sheet 3.

Let me know if more details are needed.

 

[alansidman]

Bring each sheet into Power Query/Get & Transform.
Merge the two tables with a Left Inner Join. Here is the Mcode for that procedure. Close and Load the VB Editor to a new sheet.

let
    Source = Table.NestedJoin(Table1, {"Column1"}, Table2, {"Column1"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column1"}, {"Column1.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Column1.1] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1.1"})
in
    #"Removed Columns"

Book6
	A
1	Column1
2	A
3	C
4	M
5	K
6	Y
Sheet3