What function could I use to:

  • Thread starter Thread starter Steven
  • Start date Start date
S

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