Apr 25 2023 01:23 AM
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
Apr 25 2023 09:04 AM
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)))
)
)
)
)
Apr 25 2023 11:49 AM
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
Apr 25 2023 12:35 PM
SolutionI'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.
Apr 25 2023 12:47 PM
Apr 25 2023 12:35 PM
SolutionI'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.