Using UNIQUE with non-adjecent columns on different sheets

Question

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

score 4 · Accepted Answer · answered Nov 06 '21 at 04:23

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

This is great!!! And supports me to prepare a question about best practice regarding the use of LAMDA and LET (Although I don't yet have LAMBDA) — Ike, Nov 06 '21 at 08:58

score 2 · Answer 2 · answered Nov 04 '21 at 12:20

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

answered Nov 04 '21 at 12:20

JvdV

53,146
6
36
60

1

I am deepy impressed - thanks. What I did to understand the formula: I pasted each element (X, Y, Z) as a single formula to the sheet to see the result. By that it is possible to understand the logic behind the formula. But I have no idea how I could build that on my own. Could you suggest tutorials or anything else to become more experienced in using the new opportunities? – Ike Nov 04 '21 at 13:43
Good to know it works! It's rather hard to explain these formulas sometimes so I don't always do so in detail. Good to see you managed yourself =) @Ike – JvdV Nov 04 '21 at 14:03
What I did to understand is rename the variables and another split (your A has become columnMatrix and mergedColumn): `=LET( mergedArray,CHOOSE({1,2},tblFruits1[Name],tblFruits2[Name]), cntCells,COUNTA(mergedArray), rowMatrix,MOD(SEQUENCE(cntCells)-1,cntCells/2)+1, columnMatrix,ROUNDUP(SEQUENCE(cntCells)/(cntCells/2),0), mergedColumn,INDEX(mergedArray,rowMatrix,columnMatrix), UNIQUE(FILTER(mergedColumn,NOT(ISNA(mergedColumn)))))` – Ike Nov 04 '21 at 14:09
1

I have never done that before: but I will switch my accepted answer to the one of @chris neilsen. It looks less complicated - even if you don't use LAMBDA yet but put the UNIQUE around the LET-function – Ike Nov 06 '21 at 09:01

score 1 · Answer 3 · answered Mar 23 '22 at 02:02

1

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

answered Mar 23 '22 at 02:02

Ryan SoothSawyerCom

11
1

`MAKEARRAY` is a cool function :-) making the solution - in my eyes - more understandable :-) But I had to remove the last `+1` to make it work. – Ike Mar 23 '22 at 07:00

12Rev79 · Answer 4 · 2021-11-06T09:52:56.990

0

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)

edited Nov 06 '21 at 09:52

answered Nov 04 '21 at 10:50

12Rev79

166
1
6

I dont't get this running - I suppose it is somehow due to "//b". What does it mean as xPath? Word-boundaries? But where do they come from. Adjusting the formula to `=UNIQUE(FILTERXML("" & TEXTJOIN("",1,T(IFNA(IF({0,1},tblFruits1[Name],tblFruits2[Name]),""))) &"","//y"),FALSE,FALSE)` worked. – Ike Nov 04 '21 at 13:40
1

@Ike `FILTERXML` has a limit on the string length it can handle of about 32000 characters (including all the xml stuff) If you have a large set of long(ish) strings, that could be exceeded. – chris neilsen Nov 06 '21 at 04:36

score 0 · Answer 5 · answered Apr 22 '22 at 04:18

0

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

answered Apr 22 '22 at 04:18

Alan McBee

4,059
3
31
38

Up to now I don't have VSTACK :-( - but where does it go in the formulas you show in your example? – Ike Apr 22 '22 at 06:53

Using UNIQUE with non-adjecent columns on different sheets

5 Answers5

Linked

Related