# Selecting rows from two multi-column lists, where values are the same in a chosen column

If I have two multi-column lists, for example:

ListA = {{1, a1}, {2, a2}, {3, a3}, {4, a4}, {5, a5}, {6, a6}, {7, a7}, {8, a8}}
ListB = {{1, b1}, {3, b3}, {4, b4}, {7, b7}, {8, a8}}


I would like to choose the rows from both lists where the elements in a particular column match, in this example column 1 for both lists. I would therefore expect the output:

ExpectedList = {{1, a1, b1}, {3, a3, b3}, {4, a4, b4}, {7, a7, b7}, {8, a8, b8}}


So far I have looked at Pick, Select and Intersection, but haven't been able to make it work for more than list. An easy (but crude) way to do this would be with a Table or loop, but there must be a better way!

ListA = {{1, a1}, {2, a2}, {3, a3}, {4, a4}, {5, a5}, {6, a6}, {7, a7}, {8, a8}}
ListB = {{1, b1}, {3, b3}, {4, b4}, {7, b7}, {8, a8}}

Flatten[{#, Cases[Join[ListA, ListB], {#, __}][[All, 2]]}] & /@
Intersection[listA[[All, 1]], listB[[All, 1]]]


{{1, a1, b1}, {3, a3, b3}, {4, a4, b4}, {7, a7, b7}, {8, a8, a8}}

KeyValueMap[Flatten @* List] @
Select[Length @ # > 1&] @ GroupBy[Join[ListA, ListB], First -> Rest]

{{1, a1, b1}, {3, a3, b3}, {4, a4, b4}, {7, a7, b7}, {8, a8, a8}}


Use GatherBy but because it accepts one list, we should join them first:

ListA = {{1, a1}, {2, a2}, {3, a3}, {4, a4}, {5, a5}, {6, a6}, {7, a7}, {8, a8}};
ListB = {{1, b1}, {3, b3}, {4, b4}, {7, b7}, {8, a8}};

GatherBy[Join[ListA, ListB], First]

(*Out: {{{1, a1}, {1, b1}}, {{2, a2}}, {{3, a3}, {3, b3}}, {{4, a4}, {4,
b4}}, {{5, a5}}, {{6, a6}}, {{7, a7}, {7, b7}}, {{8, a8}, {8, a8}}} *)


Now filter those which have a length equal to or less than 1:

Cases[GatherBy[Join[ListA, ListB], First], x_List /; Length@x > 1, 1]

(*Out: {{{1, a1}, {1, b1}}, {{3, a3}, {3, b3}}, {{4, a4}, {4, b4}}, {{7,
a7}, {7, b7}}, {{8, a8}, {8, a8}}} *)


Flatten each case and delete duplicates (for numbers):

Cases[GatherBy[Join[ListA, ListB], First],
x_List /; Length@x > 1 :> DeleteDuplicates[Flatten[x]], 1]

(*Out: {{1, a1, b1}, {3, a3, b3}, {4, a4, b4}, {7, a7, b7}, {8, a8}} *)


{8, a8} is because of the last element in ListB.

I also suggest: do not start variable names with upper case and not use built-in names in your names too.

• Nice! I'm leaving the question open, only as there is quite a few lines of code in this answer -- in fact doing it with a loop takes less! But if no one else provide a better answer you get the tick as you've answered the question. Mar 29, 2021 at 16:41
• @Q.P. There’s only one line in the code presented, just fyi. Beny builds it up for improved understanding, which is where your impression of multiple lines comes from. Mar 29, 2021 at 21:00
• @CATrevillian My mistake! I clearly didn't read Beny lzd's answer properly. Thanks for pointing this out. Mar 29, 2021 at 23:23

Using the association route, this turns the lists into associations:

ListA = Rule @@@ {{1, a1}, {2, a2}, {3, a3}, {4, a4}, {5, a5}, {6,
a6}, {7, a7}, {8, a8}};
ListB = Rule @@@ {{1, b1}, {3, b3}, {4, b4}, {7, b7}, {8, a8}};
ListA //= Association;
ListB //= Association;


Now we combine them:

intersection = KeyIntersection[{ListA, ListB}];
KeyValueMap[Prepend[First[#2], #1] &, Merge[intersection, List]]