Using formulas to filter

  • Thread starter Thread starter Randy S
  • Start date Start date
R

Randy S

Is there a way to filter a list by formulas the same way
the Data->Filter option works? I'd like to take the matrix:

Col A Col B Col C Col D Col E
===== ===== ===== ===== =====
Smith 30 $104.2 Yes 52
Jones 31 $155.3 No 51
Jones 31 $422.2 Yes 49
Freer 31 $424.3 Yes 42
Waylan 30 $322.5 No 50
Smith 31 $288.3 Yes 49
etc.

And, using a formula, filter on Col B = 31 to produce:

Col AA Col AB Col AC
====== ====== ======
Jones 31 No
Jones 31 Yes
Freer 31 Yes
Smith 31 Yes
etc.

Note that the number of columns are reduced in the final
output; Col AA is Col A filtered for all rows that have
Col B = 31, Col BB is Col B filtered for all rows that
have Col B = 31, and Col AC is Col D filtered for all Rows
that have Col B = 31.
 
Randy S said:
Is there a way to filter a list by formulas the same way
the Data->Filter option works? I'd like to take the matrix:

Col A Col B Col C Col D Col E
===== ===== ===== ===== =====
Smith 30 $104.2 Yes 52
Jones 31 $155.3 No 51
Jones 31 $422.2 Yes 49
Freer 31 $424.3 Yes 42
Waylan 30 $322.5 No 50
Smith 31 $288.3 Yes 49
etc.

And, using a formula, filter on Col B = 31 to produce:

Col AA Col AB Col AC
====== ====== ======
Jones 31 No
Jones 31 Yes
Freer 31 Yes
Smith 31 Yes
etc.

Note that the number of columns are reduced in the final
output; Col AA is Col A filtered for all rows that have
Col B = 31, Col BB is Col B filtered for all rows that
have Col B = 31, and Col AC is Col D filtered for all Rows
that have Col B = 31.
Click to expand...

You would need to construct different formulas to get the first, second,
etc, matches with column B. Having done that, it's relatively easy to pull
corresponding values from other columns. If you are still interested, look
here in the section "Arbitrary Lookups":
http://www.cpearson.com/excel/lookups.htm
 
Phew! I assume others are working on doing this with built-in functions,
but if the functions in the downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following, array entered into AA1:ACwhatever where whatever is the same
number as the rows of your datarange, will return the desired output
followed by rows of #N/A!. It assumes your data is in a range named
"datarange", and depends on the 5-column column arrangement in your
illustration. (Watch for wordwrap, although I didn't get any in my
newsreader.)

=SubArray(ArrayTranspose(ArrayReshape(MakeArray(SubArray(ArrayRowFilter1(datarange,2,31),1,1,1,ArrayCountif(ColumnVector(datarange,2),31)),SubArray(ArrayRowFilter1(datarange,2,31),2,2,1,ArrayCountif(ColumnVector(datarange,2),31)),SubArray(ArrayRowFilter1(datarange,2,31),4,4,1,ArrayCountif(ColumnVector(datarange,2),31)),1),4,4)),1,3,1,ArrayCountif(ColumnVector(datarange,2),31))
 
I'm not sure if this fits, but couldn't you just apply the filter and hide those
columns and leave everything in place?

If no, but you're willing to do a little work (not formulas), you could apply
the filter, hide the columns, select the range, but then hit
edit|goto|Special|visible cells only, then copy|paste that to where you want it.
 
Back
Top