Copy Ranges Meeting Criteria

[schoenebergercms]

I have a worksheet with school numbers and test types in a vertical file, such as
schl1 Math3
schl2 Math3
schl3 Math3
schl1 Math4
schl2 Math4
schl10 Math8

I want to create a new worksheet that contains a list of the school numbers with Math3. Then a new worksheet that contains a list of the school numbers with Math4, etc.

Can someone help?

 

[NBVC]

Try this in each sheet. You will have to replace Sheet1 with the actual name of your master sheet. Keep the ! sign at the end. You may have to adjust the ranges to suit your master sheet size. Also, for each sheet, change the 2 occurences of Math3 with the respective lookup string.

=IF(ROW(sheet1!$A2)-ROW(sheet1!$A$1)>COUNTIF(Sheet1!$B$1:$B$100,"Math3"),"",INDEX(Sheet1!$A$1:$A$100,SMALL(IF(Sheet1!$B$1:$B$100="Math3",ROW(Sheet1!$A$1:$A$100)),ROW(sheet1!$B2)-ROW(Sheet1!$B$1))))

You must confirm the formula with Ctrl+Shift+Enter, not just Enter.

And then copy down as far as you want.