formula locates common data in a cell and drops them in other shee

B

Barry Walker

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
 
F

Farhad

Hi,

You can auto filter the sheet one and use custom filter and for HYPER make
your condition like:

Equals: *HYPER or for AA
Equals: *AA etc...

when you filtered it you can copy it to another sheet too

Thanks,
 
B

Barry Walker

Hi Farhad,

I have done this already so far and that works fine, but I want something
faster than that. As in it will just find them and drop them. Maybe a macro
but preferably a formula.

regards

Blade
 
I

Idoia

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)
 
B

Barry Walker

Yess that is what I was hoping you or someone may be able to help me with. Do
you no anything like this? or anyone else.

Your help is very much appreciated

Regards

Blade
 
I

Idoia

OK, I messed up one of the relative references. The formula in cell a1,
sheet2 is:

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

PD: Can I edit my posts?
 
B

Barry Walker

Hi there,

It seems I am very close to what I want to achieve. The formula picks up
only column A of my spread sheet into sheet 2 and drops it in with all the
rest being NA's. Im not sure what to do.
 
I

Idoia

Sorry, see my edit, I messed up one reference...

The formula in cell a1,
sheet2 is:

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

Also, for cells k2 and l2 in sheet1:

cell k2:=IF(RIGHT(G2,2)="AA",1,0)+K1
cell L2:=IF(RIGHT(G2,2)="PR",1,0)+L1

I guess attention to detail is not my strength!!
 
M

Max

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.
 
Top