Forum Discussion
Solved
Formula to construct filtered dynamic arrays
I would like to have an efficient way to construct a dynamic array from existing data. Assume Sheet1 with data having column headers and Sheet2 used to focus on a column of data in Sheet1. Currently ...
Robert_wood How about this one then?
=LET( filtered, FILTER( PythonData, PythonData[#Headers] = $A$1 ), FILTER(filtered, filtered <> "") )
Best way is to either:
a) have the Python program modified to ensure there is a Table containing the data on Sheet1, or
b) make the Python-produced data on Sheet1 into a Table after it's produced
That way, finding and returning a column is easy. Suppose the column name you want to retrieve is in cell $A$1 and the table is called "PythonData", then:
=FILTER(PythonData,PythonData[#Headers]=$A$1)
flexyourdata, I like your suggestion. Very powerful. However, it causes blank cells in the table to be replicated as cells with a value of 0 in the result. This makes functions like AVERAGE to fail.
Robert_wood How about this one then?
=LET( filtered, FILTER( PythonData, PythonData[#Headers] = $A$1 ), FILTER(filtered, filtered <> "") )