MicrosoftI get to play with DA Functions - my goal is to implement SELECT and UNION functionality (actually missing but required)
I find how to implement SELECT and I can't implement UNION - any help is required
SELECT - producing partial subset of area (spilled area, table or range) containing only some columns.
surprice - FILTER function help
Normally =FILTER(TABLE;bool-VECTOR) works for vertical filtering becouse bool-Vector is vertical (column based)
(NB! on samples - normal english local uses , (comma) and ; (semicolon) as list separators,
locals, where , (comma) is decimal point, list separators are ; (semicolon) and \ (backslash) accrordingly vertical and horizontal lists (vectors)
Ants 1 2 4 =FILTER(TABLE; {TRUE;FALSE;TRUE} produces Ants 1 2 4
Henn 2 7 6 Peter 3 6 9
Peeter 3 6 9
but using horizontal vector =FILTER(TABLE; {TRUE\FALSE\FALSE\TRUE}) you get following
Ants 4
Henn 6
Peeter 9
That techique allows create SQL-SELECT like behaviour
NOw for UNION there is need for operator or function adding ranges (areas, tables) together. I was surpriced that range operators dont war all
Ranges (areas) have 3 operators (again I'm using local locality - one ; (semicolon) have in english , (comma)
RANGE1 space RANGE2 - produces intersection of 2 ranges
RANGE1 colon RANGE2 - produces new RANGE containing both
RANGE1 semicolon RANGE2 - produces "multiregion" range (in english locals RANGE1 , RANGE2)
All those 3 operators works fine with SUM, COUNT and other aggregates, but DA functions and spilled formulas recognizes only 1st
Try formula: =TABLE1 a:c
this works fine and produces 3 columns cutted from table1
but formula: =SORT( (TABLE1;TABLE2) ; 1) - don't work
So I think there is need for multirange operations or special =UNION function
PS! Horizontal filter is fine, but =SELECT functions sounds better
