View Single Post
#7
May 17th 05, 09:40 PM
 Domenic Posts: n/a

Actually, since your criteria will vary in number and range of columns,
and you no doubt want to keep that flexibility, you can continue to use
AutoFilter to filter your data and use the following formula system to
help return the desired results...

Assumptions:

1) Sheet1 contains your source data

4) Column C contains your 'Names'

Formulas:

On Sheet2....

A1: enter a 0 (zero)

A2, copied down:

=IF(SUBTOTAL(3,Sheet1!A11),LOOKUP(9.99999999999999 E+307,\$A\$1:A1)+1,"")

B1:

=LOOKUP(9.99999999999999E+307,A:A)

C2, copied down:

=IF(ROW()-ROW(\$C\$2)+1<=\$B\$1,MATCH(ROW()-ROW(\$C\$2)+1,\$A\$2:\$A\$16,0),"")

D2, copied down:

=IF(N(C2),INDEX(Sheet1!\$C\$11:\$C\$25,C2),"")

E2, copied down:

=IF(N(C2),IF(D2=D1,0,IF(COUNTIF(\$D\$22,D2)-1,(ROW()-ROW(\$D\$2))-LARGE(IF(
\$D\$22=D2,ROW(\$D\$22)-ROW(\$D\$2)+1),2),ROW()-ROW(\$D\$2))),"")

Now, all you have to do is filter your data using Excel's AutoFilter and
the desired results will automatically be returned on Sheet2.

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi Domenic,

Thanks for reply. Just wanted to clarify: my criterion/criteria is based on
text or numeric values from any range of columns that have numerous unique
text and numeric values; so the criteria varies and changes - it isn't
really any one particular criterion and that's why the AutoFilter lends
itself so well to the on the fly filtering.

However, for example, I may filter on age.

I am not familiar with Aladin's Formula system - will this be a problem?

Thanks
Sam