+ Reply to Thread
Results 1 to 6 of 6

Copy filtered data & paste into adjecent column with filter applied in Excel 2007

  1. #1
    Registered User
    Join Date
    03-27-2009
    Location
    Surat,India
    MS-Off Ver
    Excel 2007
    Posts
    52

    Copy filtered data & paste into adjecent column with filter applied in Excel 2007

    Dear Masters,

    I have below data in my sheet with Column heading as (Grade,Number,Copy Number).

    Grade Number Copy Number
    A 25
    B 26
    B 30
    A 25
    A 23
    B 25
    B 26
    A 23
    A 34
    C 25
    C 25
    A 25

    I applied Autofilter gith Grade-A as selection Criteria. Now I want to copy & paste value of Number Column (relevant to A) to Copy Number Coulmn with Filter Applied. When I do using Copy & Paste it also copied value of B&C grade. Please provide the solution.

    Regards,

    Vishnu

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Copy filtered data & paste into adjecent column with filter applied in Excel 2007

    U can use this formula
    =SMALL(IF($A$2:$A$13=E2,B2:B13,""),ROW(INDIRECT("1:"&ROWS(2:13))))

    Include it in ISERROR to get rid of NUM error.

    See attachment

    Or u can use advanced filter instead., In this case u'll need to copy results in another location ... in my opinion
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, «Born in USSR»
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Copy filtered data & paste into adjecent column with filter applied in Excel 2007

    If u use the formula, then u can copy it and paste only values (paste spexial).
    Here's...

  4. #4
    Registered User
    Join Date
    03-27-2009
    Location
    Surat,India
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Copy filtered data & paste into adjecent column with filter applied in Excel 2007

    Dear Vasul,

    I don't want use formula. With Autofilter applied I want to Copy the Data. from Column B to Column C

    Is it Possible?

    Vishnu

  5. #5
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Copy filtered data & paste into adjecent column with filter applied in Excel 2007

    A simple solution is this after applying Autofilter Just write the value in column C and drag it down value will only be copied in visible cells only and remaining will be stand blanks .......
    If this post helps, Please don't 4get to click the star icon located at the bottom left of my Post.

  6. #6
    Registered User
    Join Date
    01-12-2012
    Location
    Sharjah
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Copy filtered data & paste into adjecent column with filter applied in Excel 2007

    Dear Vishnu,
    There is one little procedure which gives exactly the same effect as you are looking for. For this I need one blank coloumn somewhere after whatever data you have. I assume Column D is available as blank to me and I call it "Criteria". So currently Grade, Number, Copy Number and Criteria are columns A, B, C, and D respectively. Now follow the following steps:
    1. Filter "Grade" for "A" or whatever criteria you choose.
    2. Type "1" in the first visible cell in "Criteria"
    3. Copy first visible cell of "Criteria" and select all cells below this cell in "Criteria" column and press alt+; (i.e. select visible cells only).
    4. Paste the copied selection (Basically fill the visible range in "Criteria" with "1")
    5. Clear the filter
    6. Copy Range B:B
    7. Paste on Range E:E (Additional column after "Criteria")
    8. Apply filter on "Criteria" for "Blanks", and delete all visible cells in Range E:E
    9. Remove Filter and copy Range E:E
    10. Select Range C:C and Paste Special (Formulas or Values as you like)+ Skip Blanks.
    11. Delete Range D:E
    12. Filter "Grade" for "A" or whatever criteria you choose.


    Even though the process looks a bit long, applying it takes less than a minute once you get used to.
    Hope it helps.

    Anil Saini.


    Quote Originally Posted by hifliers View Post
    Dear Masters,

    I have below data in my sheet with Column heading as (Grade,Number,Copy Number).

    Grade Number Copy Number
    A 25
    B 26
    B 30
    A 25
    A 23
    B 25
    B 26
    A 23
    A 34
    C 25
    C 25
    A 25

    I applied Autofilter gith Grade-A as selection Criteria. Now I want to copy & paste value of Number Column (relevant to A) to Copy Number Coulmn with Filter Applied. When I do using Copy & Paste it also copied value of B&C grade. Please provide the solution.

    Regards,

    Vishnu

+ 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