Excel Filtering

Hi

I want to automatically filter records which meet a certain criteria from one long list of records on a worksheet to another worksheet which hoepfully should display these specific records only. I have tried lookups, autofilter and macros but am getting no where fast.

I want to try this for at least three different criteria

Any ideas/suggestions?

Cheers

I may be able to provide you with a solution involving a formula system (several formulas), where you input your three criteria into three different cells and a list automatically generated.

Can you post a sample of your data along with the criteria involved?

Hi there,

Here is the sample of some of the data I wish to perform filters on.

Surname First Name Code Active Finished W/Drawn
Brown James 1111 Y
Briggs Johnny 2222 Y
Brown Bill 3333 Y

This data is held on the main worksheet, I wish to filter it onto the relevant worksheet i.e Active, Finished or W/Drawn. Could your formulas do this?

Much appreciated if this is possible

Thanks


ExBeginner

Assuming that Sheet1 contains the source data, enter the following in Sheet2...

A1: enter your criterion (Active, Finished, or W/Drawn)

B1: enter 0 (zero)

B2, copied down:

=IF((Sheet1!A2<>"")*(INDEX(Sheet1!D2:F2,MATCH($A$1,Sheet1!$D$1:$F$1,0))="Y"),LOOKUP(9.99999999999999E+307,$B$1:B1)+1,"")

C1:

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

D2, copied down:

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

E2, copied down and across:

=IF(N($D2),INDEX(Sheet1!A:A,$D2),"")

Now all you have to do is change the criterion in A1 and a new filtered list will automatically be generated.

Also, if you prefer, you can hide Columns B, C, and D.

Does this help?

Hi

Have given your solution a try, and it does work but is there a way of the data being filtered to the relevant sheet, and then being deleted in the main sheet i.e. sheet1?

Thanks for your help so far, much appreciated

is there a way of the data being filtered to the relevant sheet, and then being deleted in the main sheet i.e. sheet1?

Not using a formula system. That would require VBA.

Any help with building the macro to filter the data?

Thanks again