Sunday, January 27, 2013

yogi_Extract Unique Values From Column A Of Sheet1 Sheet2 Sheet3 Filtering Out Entries Greater Than A Specified Number And Sort In Ascending Order


                                          Google Spreadsheet  Post  #991

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 27, 2013
user Testus said:(http://productforums.google.com/forum/?zx=rq2ugebf7h4a#!category-topic/docs/spreadsheets/BgPI0d9qyQ8)

this post addresses another question in the preceding thread ...

I ran into an additional problem.

I need to filter out any number above 3999 that gets retrieved from the other sheets. I can't wrap my head around how do it with such a complex formula.

=ArrayFormula(sort(unique(TRANSPOSE(SPLIT(CONCATENATE(Jan!A2:A&CHAR(9);Feb!A2:A&CHAR(9);Mar!A2:A&CHAR(9);Apr!A2:A&CHAR(9);Mai!A2:A&CHAR(9);Jun!A2:A&CHAR(9);Jul!A2:A&CHAR(9);Aug!A2:A&CHAR(9);Sep!A2:A&CHAR(9);Okt!A2:A&CHAR(9);Nov!A2:A&CHAR(9);Des!A2:A&CHAR(9));CHAR(9)))))) 
---------------------------------------------------------------------------------
assuming entries in column A of Sheet1 Sheet2 Sheet3 are all numeric, following is a solution to the problem