Excel Challenge – How to paste data to a filtered list in Excel

June 9th, 2011 by Tips Master | Posted under Excel.

ChallegeHow to copy paste data from a filtered list to another filtered list? This is a constant query in many of the Excel forums. I have tried my way of solution for this. Readers can also take this as a challenge and come with your solutions in the comments. No VBA please.

 

 

 

 

The Problem

We have a list of data in one sheet. (I have used a simple list of fruits name for a quick illustration)

List 1

In another page we have another list which is incomplete and we need to copy the apples from the first list to the other. Both the lists are not not in the same order.

 

List 2

The normal way users try a way out is as follows

First Try

Filter the apples in the first list

 

Filter first list

Filter blanks in the second list and paste the values
 Filter blanks in the second list
Paste the values

 

Incorrect pasting

You can see that the list pastes inside the filtered list which is not the correct way.

Second Try


If we try a second alternative to paste in the visible cells only using the go to command

Go to visible cells

We will get a error message like this

Information cannot be pasted
We didn’t succeed with both the ways. So whats the way out ?

 

The Solution

This might be not the best one , but this works

We will do some serial tagging to come around the problem

Do serial tagging for both the list. This is to preserve their original position
 Numbering the lists

Now sort both the data based on the column B

Sort the column B

Now filter the apples from the first list copy the data and filter the blanks cells from the end list and paste the data

 

Paste data

Now sort the lists based on the Seriel number

Paste data

We have solved the issue…..Remove the serial numbers once you complete.

 

Challenge for Readers

Is this useful to you?  How do you solve this ? Do you have any better solutions ? No VBA please .  Give me your solutions in the comments.

Tags:

Comments

11 Responses to “Excel Challenge – How to paste data to a filtered list in Excel”
  1. Tayyab Hussain says:

    This is indeed a very good tip. Thanks

  2. vinod says:

    Copy paste rule:

    1. when you copy more than one filtered cells and paste to other filtered table – copied data will paste in the order and replace old list order – 1st try.

    2.You can’t copy paste multiple cells(visible cells) into the multiple visible cells – 2nd try

    3. You can copy one cell and paste to the filtered table( without selecting visible cells) – in the above case as we are pasting only one word”apple” this will work.

    Vinod.

  3. Mudit Mathur says:

    Amazing Tip!! Thanks for this simple solution. Saved me a lot of time

  4. Deepu says:

    Thank you gobish for the help

  5. ravihanok says:

    Yeah its very good technical answere you are a great man

  6. Porthos says:

    Alternate solution:

    First of all thanks for the wonderful method. While trying your method, accidentally i found another solution.

    this method can be used if within same table one wants to copy/paste filtered cells to another column.
    - color the cells that you want to copy (use same color). filter column using that color.
    - now you can’t copy/paste by selecting these filtered color cells and using normal copy paste commands, as the article tells.
    - the trick is to select all colored cells and then use mouse to simply drag and paste in the next column. cells will be copied in corresponding areas.
    - in case the column where data needs to be pasted is not adjoining the main column (where data lies), hide inbetween columns and do the above step, and then again unhide the hidden columns.

    Hope this works for all of you!

  7. Ron horne says:

    Smashing solution!
    while other solutions on the web take you to Galapagos and back this one does it with the minimal effort.

  8. duong trung duong says:

    It is very useful tip Thank so much.

  9. ema says:

    yes, very good, cheers!

  10. Graeme says:

    First of all, nice solution. Unforunately it won’t work for me, as I have a VBA script which filters thousands of rows before I get working on it, and I don’t want to lose that filter.

    I still don’t get why Microsoft doesn’t fix this bug!

    Essentially, Copying and Pasting have different rules.

    You can only copy visible data, not hidden data.
    You can only paste over hidden data, not just visible data.

    This is totally inconsistent!!!

    I’m sure it worked in Excel 2003, where you could copy and paste only in visible rows.

    Very frustrating as I have to do this work every month, and now a lot of it has become manual work since upgrading to Excel 2010.

  11. Cliff T says:

    @porthos — you have found gold my friend…

    As most people like myself copy vertically I forgot you could copy horizontally as well.
    You don’t need any special colours to copy a filtered list to the column beside it… you just filter your list and then shift select the entire list and at the bottom cell on the right corner you hover on that corner and then you drag and drop to the right… simple easy and no macros, no special go to tricks, nada… just simple drag and drop…

    works in Excel 2010 and likely other versions…
    wow oh wow

Do you have any comments on Excel Challenge – How to paste data to a filtered list in Excel ?