Hello
Trying to combine features of the Match() function with Autofilter, but having a brain-freeze. Here’s what I am trying to accomplish.
The spreadsheet contains a database from multiple sample points. I am using AutoFilter to display the data for each sample point, one at a time. Say the database has 300 rows, each sample point may have between 5 and 15 rows. One column contains a formula that designates whether the row is a candidate for additional calculations. It looks something like this (additional columns supressed):
Filter HD SD
0 0.00 0.00
1 -2.91 0.73
1 -1.48 -0.22
1 -1.00 0.00
0 0.00 0.00
0 0.00 0.00
0 0.00 0.00
The “1” in the Filter column indicates the row should be processed further while the “0” indicates it should not be included in subsequent calculations. The tricky part is that the FIRST and the LAST occurrence of the “1” are vital for the additional calculations. There’s the problem: what formula can I put into some other cells that will return the value of the FIRST occurrence of HD, the FIRST occurrence of SD, the LAST occurrence of HD, and the LAST occurrence of SD for the rows that are made visible by AutoFilter. They are in variable positions depending on the value of the AutoFilter.
I tried with a MATCH() function, and it works OK if the formula is hard-wired to the exact rows that are currently made visible by the AutoFilter e.g. =MATCH(1,N38:N44) returns “4”, which is the relative rownumber of the last occurrence of the “1”. But when I generalize the formula to include ALL the rows of the database, the same formula returns “44”. It also displays “44” regardless of which rows have been made visible by AutoFilter.
If it helps, there is a cell on the spreadsheet that contains the value that is the current AutoFilter.
Any ideas how I can solve this problem? TIA.
