How to match cross-referenced names from table without duplicates

Question

savvy people,

I will have participants of an event sign up where they, aside from their personal details, also provide a duo partners name or leave that blank. So, I will have two columns, participants (A2:A99) and partners (B2:B99). The partner of each participant will also sign up themselves and refer their partners name. I wish to create another table providing two columns (D2:D99 and E2:E99) with duo names, where person A must have referenced B and vice versa. In another table (G2:G99), I wish to produce a list of people who have not yet had their partner sign up, or who have not provided a partners name. With some data, assume the following example and expected result:

Entry:

Participant	Partner
Alpha	Bravo
Foxtrot	Echo
Charlie	Delta
Bravo	Alpha
Lex
Echo	Foxtrot
Hotel	India
Delta	Charlie

Expected result:

Participant	Partner
Alpha	Bravo
Foxtrot	Echo
Charlie	Delta

Unmatched
Lex
Hotel

I have thought of a recurring entry for each cell. In D2:E2 I'd have the first duo match. Then in D3, I'd look up the first unique name in A2:A99 that is not in $D$2:$E3, find its partners name and check whether they also have written their name. Another out of the box idea and likely stupid idea I came up with was assigning a unique prime number, multiplying the values together in C2:C99, and finding which values occur double, prime factoring to find each corresponding name for each unique prime factor. With Python in mind, I immediately thought of dictionaries to find unique pairs. However, I don't know how to translate that into working Excel formulas. Would anybody be able to help me :)?

Kindly, Lex.

Answer 1

These are really 2 questions.

Part 1:

=FILTER(A2:B9,SEQUENCE(ROWS(A2:A9))<IFNA(XMATCH(A2:A9,B2:B9),0))

Or:

=LET(a,A2:B9,
     x,TAKE(a,,1),
     y,DROP(a,,1),
FILTER(a,SEQUENCE(ROWS(a))<IFNA(XMATCH(x,y),0)))

Part 2:

=FILTER(A2:A9,ISNA(XMATCH(A2:A9,B2:B9)))

Or:

=LET(a,A2:B9,
     x,TAKE(a,,1),
FILTER(x,ISNA(XMATCH(x,DROP(a,,1)))))

Answer 2

First, you can verify a whether participant is properly matched by checking whether its partner's partner is indeed itself.

e.g for participant Alpha, in row1, you can see its partner is Bravo, then you do a lookup, XLOOKUP(Bravo, lookup_array = participants, return_array = partners). if the result of that XLOOKUP is indeed Alpha, that means the pair is properly matched.

The formula would be:

=LET(
    participants, A2:A9,
    partners, B2:B9,
    reversed_partners, XLOOKUP(partners, participants, partners, ""),
    matched, (reversed_partners = participants) * (partners <> ""),
    matched
)

Then, you don't want to include both (Alpha, Bravo) and (Bravo, Alpha) in your results table, so , you need a running count to check.

That is, as you add matched pair (participant, partner), to your results table, you need to check whether the participant of the pair is already included in any of the "partner" column of previous rows.

The running count formula is COUNTIF:

=LET(
    participants, A2:A9,
    partners, B2:B9,
    running_counts, MAP(
        SEQUENCE(ROWS(partners)),
        LAMBDA(row_num,
            COUNTIF(INDEX(partners, 1):INDEX(partners, row_num), INDEX(participants, row_num))
        )
    ),
    running_counts
)

You can put the above two in one formula:

=LET(
    participants, A2:A9,
    partners, B2:B9,
    reversed_partners, XLOOKUP(partners, participants, partners, ""),
    matched, (reversed_partners = participants) * (partners <> ""),
    running_counts, MAP(
        SEQUENCE(ROWS(partners)),
        LAMBDA(row_num,
            COUNTIF(INDEX(partners, 1):INDEX(partners, row_num), INDEX(participants, row_num))
        )
    ),
    HSTACK(matched, running_counts)
)

For your matched table, you need pairs that are properly matched, and with running_count = 0:

So your filter formula can be written like this:

=FILTER(A2:B9,(C2:C9)*NOT(D2:D9))

For your unmatched results, you just need to filter out those with "Matched = 0":

=FILTER(A2:B9,NOT(C2:C9))