[SOLVED] Using UNIQUE with non-adjecent columns on different sheets

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

The VSTACK function makes the Union obsolete (only available to insiders at time of writing)

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