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