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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,011
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,119,061
Messages
5,575,872
Members
412,689
Latest member
nhsmedic
Top