Transferring only certain rows to seperate worksheet

  • Thread starter Thread starter JRD
  • Start date Start date
J

JRD

Is there a function in excel 2007 and 2003 for picking only certain rows /
cells from a worksheet and copying them to a seperate worksheet in the same
document

e.g.

A B C D
NAME PRIORITY ALIVE? LAB NO

1 JD ELECTIVE Y 1
2 AN ELECTIVE N 2
3 ST URGENT Y 3
4 AN URGENT Y 1
5 JD URGENT N 2
6 JD ELECTIVE Y 3
7 ST ELECTIVE N 2

How can I pick out all the rows where column A (Name) is JD and column B
(priority) is elective and then copy the data in columns A, B and C only in
these rows only to another worksheet in the same document.

Therefore on a seperate work sheet the following would appear for the
example above:

A B C
NAME PRIORITY ALIVE?

1 JD ELECTIVE Y
2 JD ELECTIVE Y
3 ST URGENT Y

Can excel 2003 or maybe 2007 do this?

Thanks

John
 
Hi Mike,

Many thanks

Excuse my ignorance, but what is worksheet code and how and where do I enter
it. Do I put it in a cell?

John
 
Hi Mike,

Many thanks

Excuse my ignorance, but what is worksheet code and how and where do I enter
it. Do I put it in a cell?

John
 
Try this:

sheet 2:
Header in row 1

A2:
=IF(ISERR(SMALL(IF((NAME="JD")*(PRIORITY="ELECTIVE"),ROW(INDIRECT("1:"&ROWS(NAME)))),ROWS($1:1))),"",INDEX(INDIRECT(A$1),SMALL(IF((NAME="JD")*(PRIORITY="ELECTIVE"),ROW(INDIRECT("1:"&ROWS(NAME)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy across and down as far as needed
 
Try this:

sheet 2:
Header in row 1

A2:
=IF(ISERR(SMALL(IF((NAME="JD")*(PRIORITY="ELECTIVE"),ROW(INDIRECT("1:"&ROWS(NAME)))),ROWS($1:1))),"",INDEX(INDIRECT(A$1),SMALL(IF((NAME="JD")*(PRIORITY="ELECTIVE"),ROW(INDIRECT("1:"&ROWS(NAME)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy across and down as far as needed
 
Hi,

On the sheet with the source data, right click the sheet tab, view code and
paste the code in on the right. In the code change DestSheet to the worksheet
you want the data pasted into and then run the code by pressing F5

Mike
 
Hi,

On the sheet with the source data, right click the sheet tab, view code and
paste the code in on the right. In the code change DestSheet to the worksheet
you want the data pasted into and then run the code by pressing F5

Mike
 
Hi,

If you are looking for a non formula based approach, you may use advanced
filters. Assume that the data is in range A1:D8 (including headings). In
A11:B11, type name and priority. In A12:B12, type JD and Elective. Now go
the next worksheet and type Name, Priority, Alive in range B4:D4. Now click
on cell B6 of this sheet and go to Data > Filter > Advanced Filter. In the
Action group, select copy to another location. in the list range, select
A1:D8 of the previous sheet (where the data is). In the criteria box,
select A11:B12 of the sheet where the data is. In the copy to box,
highlight B4:D4 of the Output sheet. Now click on OK.

Please note that this is not a dynamic solution. Everytime the base data
changes, you will have to rerun the advanced filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Hi,

If you are looking for a non formula based approach, you may use advanced
filters. Assume that the data is in range A1:D8 (including headings). In
A11:B11, type name and priority. In A12:B12, type JD and Elective. Now go
the next worksheet and type Name, Priority, Alive in range B4:D4. Now click
on cell B6 of this sheet and go to Data > Filter > Advanced Filter. In the
Action group, select copy to another location. in the list range, select
A1:D8 of the previous sheet (where the data is). In the criteria box,
select A11:B12 of the sheet where the data is. In the copy to box,
highlight B4:D4 of the Output sheet. Now click on OK.

Please note that this is not a dynamic solution. Everytime the base data
changes, you will have to rerun the advanced filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top