How to compare and eliminate un-needed columns

[dixiebritt]

I have 2 spreadsheets. One spreadsheet has the following:

Last Name	First Name	Social
Doe	Jane	123-45-6789
Smith	John	111-11-1111
Appleseed	John	555-55-5555

<tbody>
</tbody>

And another Spreadsheet has a list of names (all in DB) with their address.

Last Name	First Name	Address	City	St	Zip
Doe	Jane
Doubtfire	Laura
Smith	John
Appleton	Tammy
Appleseed	John

<tbody>
</tbody>

I am needing a way to merge the 2nd table to the first table, only bringing the data for those who have "matching last and first names). There are thousands of lines of data, so hoping there is a way of doing this a bit more simply than manually going through each.

 

[ShawnPCooke]

You can use a VLOOKUP. The first thing you need to do, however, is to create a key field in both spreadsheets consisting of both of the name fields.

In your example above, the key field might be "DoeJane", "SmithJohn", and "AppleseedJohn". You can accomplish this with the concatenation operator &.

Then, use a VLOOKUP from one spreadsheet to the other, looking up the key field and outputting the particular column of data you want.

 

[sandy666]

try PowerQuery

Table10

Table11

Result

Last Name

First Name

Social

Last Name

First Name

Address

City

St

Zip

Last Name

First Name

Social

Address

City

St

Zip

Doe

Jane

123-45-6789

Doe

Jane

Doe

Jane

123-45-6789

Smith

John

111-11-1111

Doubtfire

Laura

Smith

John

111-11-1111

Appleseed

John

555-55-5555

Smith

John

Appleseed

John

555-55-5555

Appleton

Tammy

Appleseed

John

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source,{"Last Name", "First Name"},Table11,{"Last Name", "First Name"},"Table11",JoinKind.LeftOuter),
    #"Expanded Table11" = Table.ExpandTableColumn(#"Merged Queries", "Table11", {"Address", "City", "St", "Zip"}, {"Address", "City", "St", "Zip"})
in
    #"Expanded Table11"[/SIZE]