Selection of Data in an Array Based on Specific Criteria

I have one set of data that exists in sheet 1 with multiple levels of filterable criteria. I want to be able to break out the information onto seperate sheets based on specific criteria.

For example:

If sheet 1 has the following:

1 OS A 200.01
1 CO A 300.01
2 OS A 400.01
2 CO A 500.01
3 OS A 600.01
3 CO A 700.01

I want to select all numerical codes based on the criteria of 1 and OS.

Any help would be greatly appreciated.

You could put a formula like this in the first sheet (in E2, assuming you have a header row):

Formula:

=IF(AND(A2=1,B2="OS"),MAX($E$1:$E1)+1,"")

When you copy this down you will get a simple sequence against records which match the criteria and blanks otherwise.

In the second sheet you can have this formula in A2:

Formula:

=IFERROR(MATCH(ROWS($2:2),Sheet1!E:E,0),"-")

which will find the rows that have been marked in the first sheet, then this formula in B2:

Formula:

=IF(OR($A2="",$A2="-"),"",INDEX(Sheet1!A:A,$A2))

This formula can be copied across to E2, then the formulae in A2:E2 can be copied down as far as you need them (until you see a hyphen in column A).

To set up different criteria, you just need to change the formula in Sheet1.

Hope this helps.

Pete