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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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,
 
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
 
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)
 
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
 
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?
 
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.
 
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!!
 
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.

---
 
Back
Top