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

chris neilsen · Accepted Answer · 2022-10-25T10:02:55.890

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

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 3 · Answer 2 · answered Nov 04 '21 at 12:20

3

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

70,606
8
39
70

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

score 1 · Answer 4 · answered Feb 21 '23 at 19:58

Several answers already referred to the VSTACK function to return the union of two tables or arrays, it's now available in several Excel versions. It can be combined with UNIQUE (as already said too):

=UNIQUE(VSTACK(tblFruits1[Name];tblFruits2[Name]))

Here is the screenshot of the result (tried on Excel 365):

12Rev79 · Answer 5 · 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 6 · 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,202
3
33
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
Doesn't work for me. In my version of Excel UNIQUE does not accept 2 table column references (or table references) for the array argument as is proposed in this solution. using: Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20602) 32-bit – shortski Aug 12 '22 at 13:00

P E · Answer 7 · 2022-11-11T07:22:42.143

I don't have VSTACK or HSTACK at this point (or LAMDA either), unless I resort to keeping workbooks on slow-and-clunky Excel Online. But say you have 3 dynamic arrays (named three, four and five), each with 2 columns and a variable number of rows. Then (to keep things readable) use a named formula combo defined as =SEQUENCE(ROWS(three)+ROWS(four)+ROWS(five)). Then this works:

=IFS(
combo<=ROWS(three),              three,
combo<=ROWS(three)+ROWS(four),   INDEX(four,combo-ROWS(three),{1,2}),
TRUE,                            INDEX(five,combo-ROWS(three)-ROWS(four),{1,2})                                                     
    )

You can wrap UNIQUE and/or SORT around this, if you like. If the arrays have 3 columns rather than 2 then use {1,2,3} in the formula. Obviously you can expand this to more than 3 arrays by building more conditions into the IFS formula.

Perhaps it's worth noting that three is the same thing as INDEX(three,combo,{1,2}). And using COLUMN(three) would make the syntax more generalisable, though it doesn't allow you to re-order the columns if you want something like {2,1,3} in your output. Also, TRUE is effectively how you say "ELSE" in an IFS formula--it's a bit more concise here than combo<=ROWS(three)+ROWS(four)+ROWS(five). Using these longer forms would make the formula more symmetrical but a lot wordier!

(It would be nice if VSTACK ever becomes available on the desktop.)

In the meantime VSTACK is part of the current channel (desktop) - maybe you will get it with the semi-annual rollout. Your solution is more or less a rebuild of Chris Neilsens Lambda ... — Ike, Nov 11 '22 at 07:17

Using UNIQUE with non-adjecent columns on different sheets

7 Answers7

Linked

Related