Saturday, May 18, 2013

yogi_Pull Values From Sheet 'data to filter' On Criteria In Sheet Named 'filter criteria'


                                          Google Spreadsheet   Post  #1199
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 18, 2013
user mistril  (http://productforums.google.com/forum/#!mydiscussions/docs/zLw099Kl0bc)
possible to filter a 2D array?
This is a three part problem, but it's the FIRST bit that I'm not even sure is possible...

here is a copy of my work you can edit/play with directly to see what I'm talking about: https://docs.google.com/spreadsheet/ccc?key=0Am9_MspkjnSfdFRBcnQ3dE5uRmJwWGFLUGdpMThaUmc#gid=2

(#1) I have two grids (2d arrays), both the same dimensions. The "data" is in the first, and the "criteria" is in the second. I want to return an array which only contains values in the "data" grid for which the corresponding values in the "criteria" grid are less than 8,000,000.

Can't figure that one out. If it's possible, I (#2) only want the max value (which is also problematic because max() seems to want 1xN or Nx1), and (#3) I also want to return the contents of the column and row headers (like coordinates).

If I can figure out #1, I can probably get the rest. Anyway, my end result is essentially 2 or 3 cells; so if "breaking" the 2D array in the process helps, have at it.
------------------------------------------------------------------------------------------------------------------------------
let us have a look at the following


No comments:

Post a Comment