+ Reply to Thread
Results 1 to 7 of 7

copy and paste rows based on criteria in column

  1. #1
    Registered User
    Join Date
    06-15-2012
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2010
    Posts
    46

    copy and paste rows based on criteria in column

    Please see attached document, it will help you understand what I am looking to do. I would like to have the rows that have a "Y" in column D to be compiled into the filtered worksheet; I hoping to achieve this with some sort of excel command(s), do not want to have to manually filter and copy and paste into the other worksheet each time. Kind of like I want the filtered information only to show up in the filtered worksheet, the issue is I do not want to use VBA coding to get this done. See sheet 3, it would give you an idea of what I am looking to achieve.

    I hope someone out there can help me with this, I appreciate all the help I can get. Thanks in advance.

    CharlieZangel
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: copy and paste rows based on criteria in column

    Sheet: FILTERED cell
    =IFERROR(INDEX(ORIGINAL!A$2:A$12,SMALL(IF(ORIGINAL!$D$2:$D$12="Y",ROW(ORIGINAL!$A$2:$A$12)-1,""),ROW()-1)),"")

    Its an array formula, thus enter Ctrl+Shift+Enter
    Appreciate the help? CLICK *

  3. #3
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: copy and paste rows based on criteria in column

    Sheet: FILTERED cell
    =IFERROR(INDEX(ORIGINAL!A$2:A$12,SMALL(IF(ORIGINAL!$D$2:$D$12="Y",ROW(ORIGINAL!$A$2:$A$12)-1,""),ROW()-1)),"")

    Its an array formula, thus enter Ctrl+Shift+Enter

  4. #4
    Registered User
    Join Date
    06-15-2012
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: copy and paste rows based on criteria in column

    What do you mean by it is an array formula, thus eneter Ctrl+Shift+Enter? Dont know what that means.

  5. #5
    Registered User
    Join Date
    06-15-2012
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: copy and paste rows based on criteria in column

    Thanks for your help, but waht do you mean by your lasts sentence above?

    Thanks

  6. #6
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: copy and paste rows based on criteria in column

    Quote Originally Posted by CharlieZangel View Post
    What do you mean by it is an array formula, thus eneter Ctrl+Shift+Enter? Dont know what that means.
    It means: When you enter the formula you should press Ctrl+Shift+Enter at the same time. In normal functions you press "Enter" after finishing the formula. But in the Array formula its not the case. Because its calculation is different "Excel" have to differentiate it and it can be done only by pressing CSE

  7. #7
    Registered User
    Join Date
    06-15-2012
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: copy and paste rows based on criteria in column

    Hey AZ-XL, I really appreciate your help on this, I have tried this and it doesn't seem to work. Does this have to do with the version of excel I'm running? I currently have excel 07.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1