SORT(UNIQUE(FILTER) from 2 columns

[thewiseguy]

Hi all - I currently am using the following formula:

=SORT(UNIQUE(FILTER('Line Item (input)'!AG3:AG502,('Line Item (input)'!AG3:AG502 <>"")*('Line Item (input)'!AG3:AG502<>0),"")))

I would like to add in another column into this equation:

=SORT(UNIQUE(FILTER('Line Item (input)'!AI3:AI502,('Line Item (input)'!AI3:AI502 <>"")*('Line Item (input)'!AI3:AI502<>0),"")))

Is this possible..to sort unique values form 2 separate columns?

Any help is greatly appreciated!

 

[Rick Rothstein]

Your question is a little unclear to me. Are you looking for two columns of output, each one UNIQUE'ly FILTER'ed and SORT'ed separately from the original two columns OR do you want a single column output where all the data in both columns are combined, FILTER'ed, etc.?

 

[thewiseguy]

Hi Rick. Sorry for the confusion. i would like 1 column of unique data pulled from 2 (or more) columns.

 

[Rick Rothstein]

This is untested (I'm doing this off the top of my head), but I think it should work. The key is to use VSTACK to put the data into a single column reference and process that. Also note how using LET's ability to define a variable for lengthy items makes your code more compact and easier to read.
=LET(sh,VSTACK('Line Item (input)'!AG3:AG502,'Line Item (input)'!AI3:AI502),SORT(UNIQUE(FILTER(sh,(sh <>"")*(sh<>0),""))))

 

[thewiseguy]

thank you Rick. This is a huge help!