How to find distinct values across multiple columns in excel?

Question

So lets say I have two columns on two different tabs.

tab1

col1      
apple       
banana  
grape     
strawberry
strewberry

tab2

col2
grape
berry
orange
strawberri
grape

All I would like to do is automatically have a column three which is simply an alphabetized list of unique values in both columns

Final Output

col3
apple       
banana  
berry
grape   
orange 
strawberri 
strawberry
strewberry

What version of Excel are you working with? Does it support `UNIQUE`, `SORT`, etc.? — BigBen, Sep 13 '22 at 13:27

Harun24hr · Accepted Answer · 2022-09-13T13:38:03.410

2

If you are on most Microsoft-365 with most recent release then try-

=SORT(UNIQUE(VSTACK(TOCOL(Sheet1!A:A,1),TOCOL(Sheet2!A:A,1))))

Otherwise go with FILTERXML() and TEXTJOIN().

=SORT(UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,Sheet1!A:A,Sheet2!A:A)&"</s></t>","//s")))

edited Sep 13 '22 at 13:38

answered Sep 13 '22 at 13:35

Harun24hr

Sadly Im on Version 2202 Build 14931.20660 – John Thomas Sep 13 '22 at 13:37
Lets try `FILTERXML()` with `TEXTJOIN()`. See edited answer. – Harun24hr Sep 13 '22 at 13:38

1 Answers1