Forum Discussion

Copper Contributor

Jun 19, 2020

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

excel

Excel on Mac

SergeiBaklan

Diamond Contributor

Jun 19, 2020

Chris2215

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

Chris2215
Copper Contributor
Jun 19, 2020
SergeiBaklan

That's crazy.
You can do it in Google Sheets in one line. Is that really the quickest way?
- SergeiBaklan
  Diamond Contributor
  Jun 20, 2020
  Chris2215
  
  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.
  - Chris2215
    Copper Contributor
    Jun 24, 2020
    SergeiBaklan
    
    I guess a union should be added then.
    How do we make that happen?