Forum Discussion
UNIQUE function two columns
I have two columns, A on one sheet, B on another.
I want to return a third column which is the unique values that appear in these columns.
Something like =UNIQUE(A:A,B:B) doesn't quite work.
What am I missing?
For reference, this is very quick on Google Sheets: unique({A:A;B:B})
13 Replies
I would go with Patrick2788 's solution.
= UNIQUE(TOCOL(A:B, 1))Not that I like the use of entire column referencing; it always strikes me as a 'quick and dirty' practice unless you know in advance that there are precisely 1048576 values.
- use VSTACK(arr1,[arr2]...) to UNION two or more arrays.
then apply Unique to the result.
=UNIQUE(VSTACK(A1:A100,B1:B100))- Perfect.. can you also show how to get it to skip blanks and Null values?
klivingoodwrightservicecorpcom
To skip blanks and 0 values use filter().
- Thanks - worked a treat!
Also, the columns don't need to be the same size. - Genius
Excel doesn't union ranges directly. Depends on concrete data, some work around could be like
=SORT(UNIQUE(FILTER( INDEX( (A1:INDEX(A:A,COUNTA(A:A)),B1:INDEX(B:B,COUNTA(B:B))), MOD(SEQUENCE(2*MAX(COUNTA(A:A),COUNTA(B:B)))-1,MAX(COUNTA(A:A),COUNTA(B:B)))+1, 1, INT(SEQUENCE(2*MAX(COUNTA(A:A),COUNTA(B:B)),,MAX(COUNTA(A:A),COUNTA(B:B)))/MAX(COUNTA(A:A),COUNTA(B:B))) ), NOT(ISERR( INDEX( (A1:INDEX(A:A,COUNTA(A:A)),B1:INDEX(B:B,COUNTA(B:B))), MOD(SEQUENCE(2*MAX(COUNTA(A:A),COUNTA(B:B)))-1,MAX(COUNTA(A:A),COUNTA(B:B)))+1, 1, INT(SEQUENCE(2*MAX(COUNTA(A:A),COUNTA(B:B)),,MAX(COUNTA(A:A),COUNTA(B:B)))/MAX(COUNTA(A:A),COUNTA(B:B))) ) )) )))SergeiBaklan
That's crazy.
You can do it in Google Sheets in one line. Is that really the quickest way?It depends. If both ranges are of the same known size that could be as
=UNIQUE(INDEX( (tblA[A],tblB[B]), MOD(SEQUENCE(14)-1,7)+1, 1, INT(SEQUENCE(14,,7)/7)))if size=7. Perhaps other variants exist. But again, in Excel there is no UNION operation, at least so far.
