Removing non-matching values in two columns

[samcoarse]

Hi,

Is there a formula to compare two columns of data and identify and then remove any values that are not present in both columns?

In the attached image example, both columns have each name except for the name DDD and it's corresponding number 4 in column B.

I would like to remove any non-matching names and the corresponding number. The final worksheet would look like the second image with the name DDD and the number 4 removed. Each of the remaining names & corresponding numbers are now on the same row.

Thanks.

 

[alansidman]

use XL2BB to upload sample data. Pictures cannot be manipulated.

 

[sandy666]

if you are able to use Power Query, you can try

let
    TBL1 = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
    TBL2 = Excel.CurrentWorkbook(){[Name="TableB"]}[Content],
    Merge = Table.NestedJoin(TBL1,{"Name"},TBL2,{"Name2"},"TBL2",JoinKind.FullOuter),
    Extract = Table.ExpandTableColumn(Merge, "TBL2", {"Name2", "Number"}, {"Name2", "Number.1"}),
    Filter = Table.SelectRows(Extract, each ([Name2] <> null))
in
    Filter