|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 15th, 2009, 12:50 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transferring only certain rows to seperate worksheet
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 
|
|
#2
May 15th, 2009, 01:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring only certain rows to seperate worksheet
Hi,
This is worksheet code and goes in the sheet that contains the source data. Change DestSheet to the name of the sheet you want the data pasting Sub Stance() Dim MyRange Dim copyrange As Range DestSheet = "Sheet3" lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A2:A" & lastrow) For Each c In MyRange If UCase(c.Value) = "JD" And UCase(c.Offset(, 1).Value) = "ELECTIVE" Then If copyrange Is Nothing Then Set copyrange = c.Resize(, 4) Else Set copyrange = Union(copyrange, c.Resize(, 4)) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Destination:=Sheets(DestSheet).Range("A1") End If End Sub Mike "JRD" wrote: 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
|
|
#3
May 15th, 2009, 03:12 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring only certain rows to seperate worksheet
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 "Mike H" wrote: Hi, This is worksheet code and goes in the sheet that contains the source data. Change DestSheet to the name of the sheet you want the data pasting Sub Stance() Dim MyRange Dim copyrange As Range DestSheet = "Sheet3" lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A2:A" & lastrow) For Each c In MyRange If UCase(c.Value) = "JD" And UCase(c.Offset(, 1).Value) = "ELECTIVE" Then If copyrange Is Nothing Then Set copyrange = c.Resize(, 4) Else Set copyrange = Union(copyrange, c.Resize(, 4)) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Destination:=Sheets(DestSheet).Range("A1") End If End Sub Mike "JRD" wrote: 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
|
|
#4
May 15th, 2009, 03:48 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring only certain rows to seperate worksheet
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")*(PRIORIT Y="ELECTIVE"),ROW(INDIRECT("1:"&ROWS(NAME)))),ROWS ($1:1)))) ctrl+shift+enter, not just enter copy across and down as far as needed "JRD" wrote: 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
|
|
#5
May 15th, 2009, 05:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring only certain rows to seperate worksheet
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 "JRD" wrote: 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 "Mike H" wrote: Hi, This is worksheet code and goes in the sheet that contains the source data. Change DestSheet to the name of the sheet you want the data pasting Sub Stance() Dim MyRange Dim copyrange As Range DestSheet = "Sheet3" lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A2:A" & lastrow) For Each c In MyRange If UCase(c.Value) = "JD" And UCase(c.Offset(, 1).Value) = "ELECTIVE" Then If copyrange Is Nothing Then Set copyrange = c.Resize(, 4) Else Set copyrange = Union(copyrange, c.Resize(, 4)) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Destination:=Sheets(DestSheet).Range("A1") End If End Sub Mike "JRD" wrote: 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
|
|
#6
May 16th, 2009, 05:36 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transferring only certain rows to seperate worksheet
Hi,
If you are looking for a non formula based approach, you may use advanced filters. Assume that the data is in range A1  8 (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 4. 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 8 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 4 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 "JRD" wrote in message ... 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
