'Using UNIQUE with non-adjecent columns on different sheets

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

Solution 1:^[1]

Since finding the Union of several ranges is a quite usefull function on its own, I use a LAMBDA to do that. The output of that can then be passed to UNIQUE

The Lambda, which I call, unimaginatively, UNION

=LAMBDA(tabl1, tabl2,
        LET(rowindex, SEQUENCE(ROWS(tabl1)+ROWS(tabl2)),
            colindex, SEQUENCE(1,COLUMNS(tabl1)),
            IF(rowindex<=ROWS(tabl1), 
               INDEX(tabl1,rowindex,colindex),  
               INDEX(tabl2,rowindex-ROWS(tabl1),colindex)
            )
        )
 )

Then

=UNIQUE(Union(tblFruits1[Name],tblFruits2[Name]))

gives the result you seek

Solution 2:^[2]

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

Solution 3:^[3]

This is a solution I created where you can replace a2# and c2# with any two arrays, dynamic arrays, etc. It also deduplicates and sorts it. This works on Excel for Mac (FILTERXML is not supported)

=LET(
firstArray, a2#,
secondArray, c2#,
totalCount, COUNTA(firstArray)+COUNTA(secondArray),
firstCount, COUNTA(firstArray),
SORT(UNIQUE(MAKEARRAY(totalCount,1,LAMBDA(r,c,IF(r<=firstCount,INDEX(firstArray,r),INDEX(secondArray,r-firstCount+1))))))
)

Solution 4:^[4]

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,(IFNA(IF({0,1},Table1[Name],Table2[Name]),"")))&"","//b"),FALSE,FALSE)

Solution 5:^[5]

There is a new function that simplifies this: VSTACK

For a unique (distinct) union (as per the original question), try this:

=UNIQUE((tblFruits1[Name],tblFruits2[Name]))

And to sort them:

=SORT(UNIQUE((tblFruits1[Name],tblFruits2[Name])))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	chris neilsen
Solution 2	JvdV
Solution 3	Ryan SoothSawyerCom
Solution 4
Solution 5	Alan McBee

'Using UNIQUE with non-adjecent columns on different sheets

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]