I have two tables on two sheets - let's say tblFruits1 and tblFruits2. Both have a column "Name". Apple - for example - exists on both lists. The lists might have a different number of rows
tblFruits1 on Sheet1
| Name | Color |
|---|---|
| Apple | red |
| Peach | yellow |
| Ananas | yellow |
tblFruits2 on Sheet2
| Name | Color |
|---|---|
| Apple | red |
| Cherries | red |
| Banana | yellow |
| Melone | green |
Now I would like to get - on a third sheet - a UNIQUE list of names of both tables.
expected result on Sheet3
| Name |
|---|
| Apple |
| Peach |
| Ananas |
| Cherries |
| Banana |
| Melone |
=UNION((tblFruits1[Name],tblFruits2[Name])) returns an error.
I tried variants with SEQUENCE and INDEX but didn't succeed.
So the question is:
How can I "construct" the matrix-parameter for UNIQUE from two column-ranges on two different sheets?
(What I am looking for is a non-VBA-solution - I know how to handle this in VBA.)
CodePudding user response：
Try:
=LET(X,CHOOSE({1,2},tblFruits1[Name],tblFruits2[Name]),Y,COUNTA(X),Z,MOD(SEQUENCE(Y)-1,Y/2) 1,A,INDEX(X,Z,CEILING(SEQUENCE(Y)/(Y/2),1)),UNIQUE(FILTER(A,NOT(ISNA(A)))))
CodePudding user response：
Can you try like this and make your Sheet1 data and Sheet2 data into Table an in your Sheet3 cell A2 copy paste the formula below
=UNIQUE(FILTERXML(""&TEXTJOIN("",1,T(IFNA(IF({0,1},Table1[Name],Table2[Name]),"")))&"","//b"),FALSE,FALSE)