Selecting rows of a table from a column of another table-CodePudding

I have a column in "table1.xlsx" with more than 200 IDs.

And another "table2.xlsx" with the IDs plus all the information about the people.

Name   Surname   ID     City
John   Wayne     54646  Madrid
Mary   Jane      11111  Berlin
Julius Randle    21321  Rome
Peter  Parker    48949  New York

I would like to extract the rows from "table2" that match with the IDs from "table1". There is an easy way?

"table3.xlsx"

Name   Surname   ID     City
John   Wayne     54646  Madrid
Julius Randle    21321  Rome
Peter  Parker    48949  New York

CodePudding user response：

Open table1.xlsx, table2.xlsx and table3.xlsx in Excel.

Go to Table1.xlsx. Select column A by clicking on A. Above that Column, you will see a box where cell number typically shows up. Click inside that and type in MyIDs.

Go to table2.xlsx. Create a new field called Matching IDs like so:

In Cell E2, type the formula:

=VLOOKUP(C2, table1.xlsx!MyIDs, 1, FALSE)

Hit enter. This formula means, take C2 (from table2) and find a matching row in table1.xlsx's named table called MyIDs (which is column A of table1). Then, choose the 1st column (which is the only column) from MyIDs. FALSE means do an exact match not an approximate match.

Click on E2. Copy it. Paste it into E3..En. You can drag to copy as well. That'll populate the formula in each cell in E column like this:

Click on sort and filter like this:

Click on Matching IDs dropdown and de-select #N/A

Highlight all rows and cells from the filtered data like so:

Copy. Go to table3.xlsx. Paste. Remove the extra column called Matching IDs at the end in table3.xlsx