Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

copy and paste to filtered data

Hi
How can I copy and paste data to a filtered data in excel?
When I tried to copy to the filtered columns (using ctlr+G) an error message comes up saying that "the copy and paste locations dont have the same size and shape"
Do you have any suggestion?
0
shunca
Asked:
shunca
  • 8
  • 7
1 Solution
 
redmondbCommented:
Hi, shunca.

You only have two choices...
(1) Select a contiguous group of cells which are a multiple of the cells being copied. This is usually not possible with filtered data.

(2) Select a single cell. Excel will paste the cells ignoring the fact that a filter is active. In all likelihood some hidden cells will be overwritten.

Can you post a sample spreadsheet showing what you're trying to achieve, please?

Thanks,
Brian.
0
 
shuncaAuthor Commented:
Thank you Brian,
I have two worksheets.
Worksheet 2 is a filtered sheet that is showing jus the TRUE rows (FALSE rows are hidden.
I need to copy the second row from worksheet 1 to the worksheet 2 filtered data.
I tried using ctrl+G but still cant't copy it to just the visible data.

Thanks

Worksheet 1                             Worksheet 2
10                  10      TRUE      
20                  20      TRUE      
30      0.280            30      TRUE      
40      0.280            40      TRUE      
50      0.219            50      TRUE       
60      0.312            60      TRUE      
70      0.250            70      TRUE      
80      0.280            80      TRUE      
90      0.280            90      TRUE      
100      0.250            100      TRUE      
110      0.250            110      TRUE      
120      0.250            120      TRUE      
130      0.280            130      TRUE      
140      0.280            140      TRUE      
150      0.280            150      TRUE      
160      0.280            160      TRUE      
170      0.250            170      TRUE      
180      0.250            180      TRUE      
190      0.219            190      TRUE
0
 
redmondbCommented:
Thanks, shunca.

I'm not usre if I've correctly understood your requirements, but if all you want to do is to replace the visisble cells by Worksheet1's Row 2 then all you have to do is copy Worksheet1's Row 2, select A2:A29 in Worksheet2 and then paste.

Regards,
Brian.Replace-Filtered.xls
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
shuncaAuthor Commented:
Thanks Brian,
Let me try to explain myself better :)
I need to copy column B from sheet 2, into sheet 1 column C. Column A & B in sheet2, should match A & C, which its the reason I have filtered the data in sheet 1. All the TRUE rows A&C in sheet 1, should match A&B in sheet 2

The issue is that when I try to copy  column B from sheet 2, into sheet 1 column C , it does it in the hidden rows as well.

Regards,
Sample1.xls
0
 
redmondbCommented:
shunca,

Two options...
(1) A macro.
(2) Unfilter and sort Sheet1 by column B. The "True" cells are now contiguous and a simple copy and paste will work.

Regards,
Brian.
0
 
shuncaAuthor Commented:
I tried sorting by TRUE and paste, the problem is that the other cells don't align properly with the information I'm pasting (i.e. number 50 doesnt align with 0.280... do you think that vlookup would work?

Thanks
0
 
redmondbCommented:
Apologies, shunca, that was my fault - I hadn't noticed the formulas in Column B.

Is this a one-off or will it be a regular job?

Thanks,
Brian.
0
 
shuncaAuthor Commented:
A regular one, have several like this one.  I guess a micro will be the best option...
right?
0
 
redmondbCommented:
shunca,

Looks like it. Give me some background on the two sheets please...
(1) Are they both created during the run or is one or both of them updated?
(2) Is Column B identifying the first entry for the code in column A?
(3) Is the file sorted by column A?

Edit: Please see attached. I've assumed that the answer to (2) and (3) is "Yes" and simplified the formula for B and added one for C. Please note that while Column C's formula uses Column B it could trivially be changed so that it didn't need to.

Regards,
Brian.Sample1-V2.xls
0
 
shuncaAuthor Commented:
1) Both are created from the same source but individualy updated.
2) Column B is identifying the type of data in column A
3) No, its sorted by other column I didn't include due to size, but the information I need to "paste" is related to column A.

Thanks
0
 
redmondbCommented:
Thanks shunca,

Both are created from the same source but individualy updated.
So an existing Sheet1 may need to be updated by a new Sheet2?

Column B is identifying the type of data in column A
It's currently identifying the first entry for a Column A "code" - but in a very expensive way. My change is much more efficient, but won't work if all the entries for a "code" aren't grouped together.

No, its sorted by other column I didn't include due to size, but the information I need to "paste" is related to column A.
So, are all the entries for a "code" grouped together?

(Apologies if you feel that all you're getting is questions when all you want is answers!)

Regards,
Brian.
0
 
shuncaAuthor Commented:
No problem,

In sheet one, the formula is to filter data and work with all the TRUE values that the formula threw.
In reality both sheets have similar data from the same source, the problem is that there are columns in sheet 2 that need to be copied to sheet 1.
Example:
Both sheets are for pickup trucks, sheet 1 has information for all trucks available. Sheet 2 has information for only blue trucks with more specific information than in sheet 1. Therefore, I need to filter sheet 1 (containing all pickup trucks) to show all blue trucks and paste a column that contains the service order for blue trucks  from sheet 2.

The highlighted row in sheet two is what I need to copy to sheet one. All the TRUE rows are the blue trucks.

Hope this helps. Thanks,
0
 
redmondbCommented:
shunca,

So, in principle, how is the attached? (It's simply V2 with your Column B formula restored.)

Regards,
Brian.Sample1-V3.xls
0
 
shuncaAuthor Commented:
Thank you Brian,
I tried that already, but when I sorted back to the original, other columns were not aligned back to the original... I think I got it now, I used vlookup and it seems working. I'm checking all the columns to make sure they all align.

Thanks for all your help, by asking me questions, made me think better and clarify what I needed to do.
Regards
0
 
redmondbCommented:
Thanks, shunca.

I normally keep an eye on "my" closed questions for at least a couple of weeks, so please feel free to post any follow up queries you may have.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now