Apr 17 2020 09:54 AM
I have a sheet titled List 1 and and List 2. I have a 3rd sheet titled "Matches". I want to only have the rows that match between the two List sheets populate in the "Matches" sheet.
How would i go about this?
Apr 18 2020 12:25 AM
Perhaps the easiest way is with Power Query - add named ranges for List1 and List 2, query both, merge with inner join and return result back. Script is like
let
Source = Excel.CurrentWorkbook(),
GetList1 = Table.SelectRows(Source, each ([Name] = "List1")){0}[Content],
List1Headers = Table.PromoteHeaders(GetList1, [PromoteAllScalars=true]),
List1Clean = Table.SelectRows(List1Headers, each ([Names] <> null)),
GetList2 = Table.SelectRows(Source, each ([Name] = "List2")){0}[Content],
List2Headers = Table.PromoteHeaders(GetList2, [PromoteAllScalars=true]),
List2Clean = Table.SelectRows(List2Headers, each ([Names] <> null)),
MatchesFromBoth = Table.NestedJoin(
List1Clean, {"Names", "DOB", "Product"},
List2Clean, {"Names", "DOB", "Product"},
"FromList2", JoinKind.Inner
),
RemoveUnused = Table.RemoveColumns(MatchesFromBoth,{"FromList2"})
in
RemoveUnused
Apr 20 2020 08:25 AM
I'm completely unfamiliar with Power Queries, is there any way to resolve this through a formula? @Sergei Baklan
Apr 21 2020 05:36 AM - edited Apr 21 2020 05:37 AM
Here are couple of more variants
If you are on Excel with dynamic arrays, when in E1
=FILTER('List 1'!$A$1:$C$8,
COUNTIF('List 1'!$A$1:$A$8,'List 2'!$A$1:$A$8)*
COUNTIF('List 1'!$B$1:$B$8,'List 2'!$B$1:$B$8)*
COUNTIF('List 1'!$C$1:$C$8,'List 2'!$C$1:$C$8)
)
If not, in I2
=IFERROR(
INDEX('List 1'!A$2:A$8,
AGGREGATE(15,6,
1/(COUNTIF( 'List 2'!$A$2:$A$8,'List 1'!$A$2:$A$8)<>0)/
(COUNTIF('List 2'!$B$2:$B$8,'List 1'!$B$2:$B$8)<>0)/
(COUNTIF('List 2'!$C$2:$C$8,'List 1'!$C$2:$C$8)<>0)*
(ROW('List 1'!$A$2:$A$8)-ROW('List 1'!$A$1)),
ROW()-ROW($A$1)
)),
"")
and drag it to the right and down