Forum Discussion

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
Apr 25, 2023
Solved

Filter with dynamic list as an condition.

Hi,   My goal here is to find which tables a name is in, and then which they are not in.   It lists perfect if the name is in 1 tabler, or in all 3. But not if the name is in 2 tables.    All 3...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Apr 25, 2023

    Hogstad_Raadgivning 

    I've modified the existing formula slightly.

     

    =LET(arrange,UNIQUE((CHOOSECOLS(B14#,2))),FILTER(arrange,ISERROR(XMATCH(arrange,L6#)),"Alle"))


    The issue with the previous arrangement was when the number of elements being compared was not the same.  The array with fewer elements threw an #N/A.

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Apr 25, 2023

Hogstad_Raadgivning 

This could be folded down a bit but here's the idea:

 

=LET(
    stack, SORT(VSTACK(t_arr1, t_arr2, t_arr3)),
    participant, TAKE(stack, , 1),
    arrange, TAKE(stack, , -1),
    uArr, TOROW(UNIQUE(arrange)),
    header, HSTACK("", "Total", uArr),
    REDUCE(
        header,
        UNIQUE(participant),
        LAMBDA(a, v,
            LET(
                filtered, FILTER(arrange, participant = v),
                VSTACK(a, HSTACK(v, COUNTA(filtered), IFERROR(SIGN(XMATCH(uArr, filtered)), 0)))
            )
        )
    )
)

Resources