Filter with dynamic list as an condition.

[Geir Hogstad]

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 tables is merged into 1 dynamic table using VSTACK. The problem is the formula in the yellow cell.

The file is attached.

Best Regards

- Geir

[#2]

@Geir Hogstad 

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)))
            )
        )
    )
)

[#3]

Thank you for your effort @Patrick2788 But it is the formula in K6, the yellow one, which is the problem. 

L6 and down list the tables the name is in. K6 and down should then liste the tables without the name in L4.

- Geir

[#4 · Accepted solution]

@Geir Hogstad 

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.

[#5]

I noticed the #N/A But this worked perfect. Thank you @Patrick2788 

- Geir

[#6]

You're welcome! Glad to help.