How to compare and eliminate un-needed columns

[dixiebritt]

I have 2 spreadsheets. One spreadsheet has the following:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Social[/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD]Jane[/TD]
[TD]123-45-6789[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]111-11-1111[/TD]
[/TR]
[TR]
[TD]Appleseed[/TD]
[TD]John[/TD]
[TD]555-55-5555[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]St[/TD]
[TD]Zip[/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doubtfire[/TD]
[TD]Laura[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Appleton[/TD]
[TD]Tammy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Appleseed[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Table10[/td][td][/td][td][/td][td][/td][td]Table11[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Result[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Last Name[/td][td=bgcolor:#5B9BD5]First Name[/td][td=bgcolor:#5B9BD5]Social[/td][td][/td][td=bgcolor:#5B9BD5]Last Name[/td][td=bgcolor:#5B9BD5]First Name[/td][td=bgcolor:#5B9BD5]Address[/td][td=bgcolor:#5B9BD5]City[/td][td=bgcolor:#5B9BD5]St[/td][td=bgcolor:#5B9BD5]Zip[/td][td][/td][td=bgcolor:#70AD47]Last Name[/td][td=bgcolor:#70AD47]First Name[/td][td=bgcolor:#70AD47]Social[/td][td=bgcolor:#70AD47]Address[/td][td=bgcolor:#70AD47]City[/td][td=bgcolor:#70AD47]St[/td][td=bgcolor:#70AD47]Zip[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Doe[/td][td=bgcolor:#DDEBF7]Jane[/td][td=bgcolor:#DDEBF7]123-45-6789[/td][td][/td][td=bgcolor:#DDEBF7]Doe[/td][td=bgcolor:#DDEBF7]Jane[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]Doe[/td][td=bgcolor:#E2EFDA]Jane[/td][td=bgcolor:#E2EFDA]123-45-6789[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Smith[/td][td]John[/td][td]111-11-1111[/td][td][/td][td]Doubtfire[/td][td]Laura[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Smith[/td][td]John[/td][td]111-11-1111[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Appleseed[/td][td=bgcolor:#DDEBF7]John[/td][td=bgcolor:#DDEBF7]555-55-5555[/td][td][/td][td=bgcolor:#DDEBF7]Smith[/td][td=bgcolor:#DDEBF7]John[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]Appleseed[/td][td=bgcolor:#E2EFDA]John[/td][td=bgcolor:#E2EFDA]555-55-5555[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]Appleton[/td][td]Tammy[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Appleseed[/td][td=bgcolor:#DDEBF7]John[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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]