• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to paste the content of non-adjasent cells to a another column?

G

GN0001

Member
I have a column with values of:
4
5
6
7
I have filtered on this column and I have selected only 4 and 6.
Go to Edit
Select only visible cells
copied 4 and 6
selected the first cell of a column where I want to paste the value
and did CTRL + V

6 is getting pasted in the adjacent In front of 5,

This is what I get
Column A Column B
4 4
5 6
6
7
What is the solution?
I appreciate your help.
GN
 
Hi GN0001

You don't need to select visible cells when a filter is on. XL does that as a matter of course.

Copy your data to a fresh sheet. Now filter on everything that is not 4 and 6 and delete the lot. Turn the filter off.

Now copy the column with the 4 and 6, the whole column. Go to the column on the original sheet you want your 4s and 6s and go Paste Special - Skip Blanks.

That will work.

Take care

Smallman
 
Another option would be to select all cells you want to fill in adjacent column click on the formula bar type = and select the cell adjacent to the last selected, then press CTRL and then Enter


i.e. you have the data in column A and want to copy in column C
Select say C1,C4,C9 (in this order) then click on formula bar type = and select A9, hold down CTRL and press Enter

If you want VALUES copy entire column and Paste Special\Value
 
This is just for fun, but there is another option :)
Say that you often need to copy the value from N columns on the left (where N is fixed)

Select a cell in the (N+1)th column (so if N=4 select a cell in column E) then add a named range, name it as N2theleft and in "Refers To" select the cell in column A and same row (N=4 --> assuming you selected E4, in refers to select A4) toggle reference to relative and save. Now, whenever you type =N2theleft you get the value from N column to the left (N=4, if you enter the formula in G4 you'll get the value stored in C4).
 
Smallman said:
Hi GN0001

You don't need to select visible cells when a filter is on. XL does that as a matter of course.

Copy your data to a fresh sheet. Now filter on everything that is not 4 and 6 and delete the lot. Turn the filter off.

Now copy the column with the 4 and 6, the whole column. Go to the column on the original sheet you want your 4s and 6s and go Paste Special - Skip Blanks.

That will work.

Take care

Smallman
Click to expand...
This is doen't work for my purpose. I want to paste as this
first row 4
second row blank
third row 6
I need to blank rows stay in between.
Regards,
GN
 
Hi, GN0001!
I don't understand what you're trying to do. The text you're quoting lets you do exactly what you want. Have you tried it?
Regards!
 
iferror said:
This is just for fun, but there is another option :)
Say that you often need to copy the value from N columns on the left (where N is fixed)

Select a cell in the (N+1)th column (so if N=4 select a cell in column E) then add a named range, name it as N2theleft and in "Refers To" select the cell in column A and same row (N=4 --> assuming you selected E4, in refers to select A4) toggle reference to relative and save. Now, whenever you type =N2theleft you get the value from N column to the left (N=4, if you enter the formula in G4 you'll get the value stored in C4).
Click to expand...
 
iferror said:
Another option would be to select all cells you want to fill in adjacent column click on the formula bar type = and select the cell adjacent to the last selected, then press CTRL and then Enter


i.e. you have the data in column A and want to copy in column C
Select say C1,C4,C9 (in this order) then click on formula bar type = and select A9, hold down CTRL and press Enter

If you want VALUES copy entire column and Paste Special\Value
Click to expand...
 
Your solution seems correct, but I don't want to move data from one sheet to a fresh sheet for a couple of reasons:
1-Data changes when we move it a lot, like the leading zeros are dropped in Excel.
2-It will be time consuming.
Thank you anyway,
GN0001
 
@GN0001
As i mentioned in the premise, the solution is a real shortcut if you frequently need to copy values form N column to the left (or any direction), as it takes a small effort initially to set things up. If it's just a once in a lifetime, then it does not worth the time.
 
You must log in or register to reply here.
Back
Top