What function could I use to:

[Steven]

Search each row on a page

(excluding some rows)

look at what's in column G

loop through each row looking for each match on G

copy that to a new page along with column I, K, M, O, for each match

continue down the page until row x, skipping rows already matched.



Many thanks,



Steve

 

[Bernie Deitrick]

Steve,

You could use Data | Filter... AutoFilter, and filter column G based on
your desired value, then select columns G, I, K, M, and O and then use Edit
go To.. Special.... Visible Cells only, copy, and paste.

Record and edit a macro to do it automatically if you are doing it
frequently.

HTH,
Bernie
MS Excel MVP

 

[Steven]

so theres no lookup or referece type of function that can do this?

Many thanks,
Steve

 

[Frank Kabel]

Hi Steven
for a formla approach try the following on a separate sheet. enter the
following array formula (with CTRL+SHIFT+ENTER) in cell A1
=INDEX('sheet1'!$G$1:$G$100,SMALL(IF('sheet1'!$G$1:$G$100="your_match_v
alue",ROW('sheet1'!$G$1:$G$100)),ROW(1:1)))
and copy down

In B1 enter the following array formula to get the data from column I
=INDEX('sheet1'!$I$1:$I$100,SMALL(IF('sheet1'!$G$1:$G$100="your_match_v
alue",ROW('sheet1'!$G$1:$G$100)),ROW(1:1)))
copy down
apply the other columns accordingly

 

[Frank Kabel]

Hi Steve
see my other post

 

[Steven]

Thanks Frank,

how can i ignore rows ? 1:12, 52:71, 111:130, 170:189, 229:236
also what is CTRL+SHIFT+ENTER meant to do? nothing happens

Regards,

Steve

 

[Frank Kabel]

Ji Steven
ignoring rows would be complicated with worksheet function. If you need
this I'd use a macro for doing this.
CTRL+SHIFT+ENTER will enter the formula as array formula (see:
http://www.cpearson.com/excel/array.htm)