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.

Related posts:

Compare Excel 2007 to Excel 2003 , File menu , Print, Page layout
Customize Quick Access Toolbar in Excel 2007
Useful Tips in Excel - Deleting blank rows, Inserting blank rows, finding out difference in Time

Tags:

Comments

6 Responses to “Excel Challenge – How to paste data to a filtered list in Excel”
  1. Tayyab Hussain says:
    June 9, 2011 at 12:25 pm

    This is indeed a very good tip. Thanks

    Reply
  2. vinod says:
    July 1, 2011 at 4:06 pm

    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.

    Reply
  3. Mudit Mathur says:
    October 19, 2011 at 11:19 am

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

    Reply
  4. Deepu says:
    December 16, 2011 at 4:09 pm

    Thank you gobish for the help

    Reply
  5. ravihanok says:
    December 29, 2011 at 6:28 pm

    Yeah its very good technical answere you are a great man

    Reply
  6. Porthos says:
    January 17, 2012 at 7:44 pm

    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!

    Reply

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