Copy paste line items in Sheet B to filtered rows in Sheet A in Excel?

Question

I have Sheet A with 500 line items.

Suppose I have applied filter to show line items "2","44","68" and so on

From Another Sheet i want to select some rows and paste them in these filtered rows.If I paste,they get copied on line items 2,3,4,5, and so one instead of 2,44,68,etc

Can Someone please help me.I tried paste special,but it has same issue.

I also tried selecting the area to paste,then clicking alt + ; but the data also gets copied into unfiltered items.

Will the number of rows to be copied will be the same as the number of filtered rows? Also are you open for a VBA solution — Siddharth Rout, Apr 26 '13 at 16:41
Yes,number of rows to be copied is same as number of filtered rows.I googled a lot and found a VBA solution.However,i dont understand it much.Thanks a lot anyways :) — Rahul Shah, Apr 26 '13 at 16:44
http://www.mrexcel.com/forum/excel-questions/85288-paste-visible-cells-only.html — Rahul Shah, Apr 26 '13 at 16:50
Yes that's the sort of logic that I had in mind. But that is for a cell and I was thinking in terms of rows. So like I said, are you open for a VBA solution? — Siddharth Rout, Apr 26 '13 at 16:54

score 3 · Accepted Answer · answered Apr 26 '13 at 17:17

3

This is more of an approach solution then a coding solution. I assume that the current order of the rows must be maintained.

Add a column to to your sheet, use autocomplete to number the current order of the rows.
Sort by your filter value(s) - if you have multiple values, just add another column and put a single unique value in it
Paste your values
Sort by you current order column, to restore the orginal order

enter image description here

answered Apr 26 '13 at 17:17

James Jenkins

1,954
1
24
43

i am not getting it exactly.....are u trying to say bring the area to be pasted on together, using Sorting on Sr.no and then copy paste? – Rahul Shah Apr 26 '13 at 17:26
hey,w8,got it......i dont want to add something extra to the filtered columns,i want to replace it with some other content. – Rahul Shah Apr 26 '13 at 17:28
Same principle just paste over the top of whatever you are replacing. – James Jenkins Apr 26 '13 at 17:29
u could sort it because it was rabbit rabbit everywhere.....the solution wont work coz evrytime it wont be same text. – Rahul Shah Apr 26 '13 at 17:31
*if you have multiple values, just add another column and put a single unique value in it * – James Jenkins Apr 26 '13 at 17:32
ok,i got an idea.....color the filtered rows.sort as per color and then copy paste...ur logic has helped to the final way.thanks a lot – Rahul Shah Apr 26 '13 at 17:40
1

@JamesJenkins you're a genius. Of course I wish it worked out of the box, but this saved my day :thumbs up: – Dheeraj Bhaskar Nov 14 '14 at 11:20

score 2 · Answer 2 · answered Aug 15 '13 at 20:52

If it is with same sheet you can make the two columns adjacent by hiding other columns in between. You can block the data and drag it to next column.

For eg.

I have column A, B, C, D.
Let A be ZIP CODE & Column D is 'Shipping Service'
I need to have ZIP CODE(Column A) with '95035' to have Shipping Service as 'Fedex'
Filter the column A with '95035'
Hide the column B & C
Drag the values from A to D directly where the values are copied to the adjacent cells.

Note: If you use two separate sheets copy the columns entirely to the sheet 1 adjacent to the column you want to copy and again take copy the entire column back to Sheet 2. This will work if both sheet have same number of rows.

Thanks, Karthik

score 0 · Answer 3 · answered Apr 26 '13 at 17:28

0

The easiest coding solution is to use vlookup http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

On sheet1 from Sheet2 use

=VLOOKUP(B3,Sheet2!A:B,2,FALSE)

enter image description here

answered Apr 26 '13 at 17:28

James Jenkins

1,954
1
24
43

i dont want to add something in a new column next to filtered rows i want to replace the filtered rows with new content. – Rahul Shah Apr 26 '13 at 17:30
Why don't you just delete the rows, after you filter? Then they will be gone and you can add new stuff at the bottom. – James Jenkins Apr 26 '13 at 17:37

Copy paste line items in Sheet B to filtered rows in Sheet A in Excel?

3 Answers3