One approach using non-array formulas ..

Assume source data in Sheet1, cols A to G, from row1 down.
The key col is col G as described.

In Sheet2,

Put in A1:
=IF(ISNUMBER(SEARCH("HYPER",Sheet1!G1)),ROW(),"")

Put in B1:
=IF(ROW()COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($ A:$A,ROW())))
Copy B1 across to H1. Select A1:H1, fill down to cover the max expected
extent of data in Sheet1's col G. Cols B to H will return the required
results, ie the lines for "HYPER" from Sheet1's cols A to G, all neatly
bunched at the top.

Now, to propagate .. just make a copy of Sheet2.

In the copy, change the formula in A1 to:
=IF(ISNUMBER(SEARCH("AA",Sheet1!G1)),ROW(),"")
Copy A1 down. No change needed to formulas in cols B to H. Cols B to H will
now return the lines for "AA" from Sheet1's cols A to G.

Just repeat the sheet copy n change for the formulas in col A for the rest
of the required captures: "PR", etc.

Note that SEARCH is not case sensitive. If you need it case sensitive,
replace SEARCH with FIND in the formulas for col A.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---