Question description:
This user has given permission to use the problem statement for this
blog.
How do I copy a filtered column with a formula and paste it to a blank column with values only?
Solved by D. U. in 19 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
16/05/2018 - 12:48
Hello
Excelchat Expert
16/05/2018 - 12:48
Hi
Excelchat Expert
16/05/2018 - 12:48
Welcome to Got it Pro
Excelchat Expert
16/05/2018 - 12:49
You can select the data that you want to copy
Excelchat Expert
16/05/2018 - 12:49
and then when you paste paste it as special
Excelchat Expert
16/05/2018 - 12:49
where you select values only
User
16/05/2018 - 12:50
when i do that it rearranges the data
Excelchat Expert
16/05/2018 - 12:50
when you paste at the same time only selct paste as values only
Excelchat Expert
16/05/2018 - 12:50
so it will give you the same value
User
16/05/2018 - 12:50
I have done that multiple times!!
User
16/05/2018 - 12:51
the concatenation seems to affect eh paste
Excelchat Expert
16/05/2018 - 12:51
I can show you on this spreadsheet.write some formula
User
16/05/2018 - 12:51
=CONCATENATE(B135," ",C135)
User
16/05/2018 - 12:52
You will need to run a filter
Excelchat Expert
16/05/2018 - 12:52
See column F
User
16/05/2018 - 12:52
To simulate what i am trying to do
Excelchat Expert
16/05/2018 - 12:52
applied the concat fucntion
User
16/05/2018 - 12:52
yes
Excelchat Expert
16/05/2018 - 12:53
Now i have pasted it on sheet 2
Excelchat Expert
16/05/2018 - 12:53
see with values only
User
16/05/2018 - 12:53
ok
Excelchat Expert
16/05/2018 - 12:53
while pasting it will show error but when you select paste as values then it will show back the values from sheet 1 only
Excelchat Expert
16/05/2018 - 12:53
I would suggest you to try again
User
16/05/2018 - 12:54
Do I paste it with or without the filter when i paste it back into the original?
Excelchat Expert
16/05/2018 - 12:54
it does not matter
Excelchat Expert
16/05/2018 - 12:55
whatever you select just paste it as values
User
16/05/2018 - 12:55
Would you wait a monment whild i try this?
Excelchat Expert
16/05/2018 - 12:55
while pasting you will see REF! but when you select paste as values it will show you the values
Excelchat Expert
16/05/2018 - 12:55
Ok
Excelchat Expert
16/05/2018 - 12:55
please try
User
16/05/2018 - 12:59
This is what happens
Excelchat Expert
16/05/2018 - 12:59
what?
Excelchat Expert
16/05/2018 - 01:00
when you paste there is an option to select paste as values
User
16/05/2018 - 01:00
see sheet 2 Col c has my paste and col e has the origiinal
Excelchat Expert
16/05/2018 - 01:00
just select that and the data will come fine
User
16/05/2018 - 01:00
I do select values
Excelchat Expert
16/05/2018 - 01:01
You pasted one row below
Excelchat Expert
16/05/2018 - 01:02
When you filter and select data then you should only compare it with the filtered data
Excelchat Expert
16/05/2018 - 01:02
not the whole data
User
16/05/2018 - 01:02
when i shoose the row it automatically pastes one row lower. It is so confusing and I have never had this issue
User
16/05/2018 - 01:02
maybe i can jsut delete the cell
Excelchat Expert
16/05/2018 - 01:03
Yes
User
16/05/2018 - 01:03
I will keep trying
Excelchat Expert
16/05/2018 - 01:03
I think you are doing right but then you are comparing with original data
Excelchat Expert
16/05/2018 - 01:03
When you put formulas and then use filter
Excelchat Expert
16/05/2018 - 01:04
Just try pasting the data but dont compare with priginal data
User
16/05/2018 - 01:04
the formyla is only on the filtered cells
Excelchat Expert
16/05/2018 - 01:04
I get it..
Excelchat Expert
16/05/2018 - 01:04
If your formula is working fine then why are you going back and comparing
Excelchat Expert
16/05/2018 - 01:04
?
User
16/05/2018 - 01:05
I have to send the sheet as a csv with no formulas
Excelchat Expert
16/05/2018 - 01:06
So just paste the data with formula on another sheet where you paste it as values
Excelchat Expert
16/05/2018 - 01:06
or you can just apply the filter first and then copy the data to another tab
Excelchat Expert
16/05/2018 - 01:06
and then use formula on it
Excelchat Expert
16/05/2018 - 01:06
This should help you i guess
Excelchat Expert
16/05/2018 - 01:07
Do you understand what i am trying to explain you?
Excelchat Expert
16/05/2018 - 01:07
Please let me know
User
16/05/2018 - 01:07
thnaks i will try
Excelchat Expert
16/05/2018 - 01:07
Cool
Excelchat Expert
16/05/2018 - 01:07
Please do give good rating and reviews
User
16/05/2018 - 01:07
yes
Excelchat Expert
16/05/2018 - 01:07
It really helps a lot
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.