In columns JKL of sheet1 write:

cell j1:=IF(RIGHT(G1,5)="hyper",1,0)
cell k1:=IF(RIGHT(G1,2)="AA",1,0)
cell l1:=IF(RIGHT(G1,2)="PR",1,0)

cell j2:=IF(RIGHT(G2,5)="hyper",1,0)+J1
cell k2:=IF(RIGHT(G2,5)="hyper",1,0)+K1
cell L2:=IF(RIGHT(G2,5)="hyper",1,0)+L1

and drop formulas down.

In sheet2, fill cloumn J with numbers 1, 2... to as mant records you have
(let's say 500).

Then, in sheet2, cell A1:

=INDEX(Sheet1!A$1:A$500,MATCH(Sheet2!J1,Sheet1!$j$ 1:$j$500,0))

and drop the formula down and right to column G.

And same for sheets 3 and 4

You'll get #NA errors once you've recovered all records you need. You can
either delete those lines, or work around it by using

=IF(ISNA(formula),"",formula)


"Barry Walker" wrote:

I have a spread sheet with a range of data from columns A - I.
The number of rows always varies. In column G there is a number or reference
followed by a a group of letters. examples below,

123456HYPER

ABCD01PC01AA

ABCD01PC01PR

I need a formula that locates the 'HYPER' of all cells of column G that
contain HYPER and drop it with the corresponding contents of cells A - I into
a seperate sheet2.

the other formula of sheet3 would pick out the 'AA' in column G and drop it
and the corresponding details into sheet3

The formula for sheet 4 would pick out the 'PR' in column G and drop it and
the corresponding details into sheet 4.

In this case then the data would essentially be sorted from sheet1 and
dropped into sheets 2 3 and 4.

is this possible?

any help would be much appreicated!

Regards

Barry