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

ssinha23

New Member
Joined
Aug 31, 2016
Messages
15
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,010
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

Rich (BB code):
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
1Column1
2A
3C
4M
5K
6Y
Sheet3
 

Watch MrExcel Video

Forum statistics

Threads
1,118,863
Messages
5,574,720
Members
412,615
Latest member
John_W_Excel
Top