Nov 10 2022 05:48 AM
Hello Experts, I have had this issue of not being able to copy data from one column to another if the list is filtered. I want to be able to copy the entire range and paste it into the same rows in the other column but excel doesnt allow it. I have to do it one by one and its time consuming.
Is there some trick to doing this?
thank you.
Nov 10 2022 06:53 AM - edited Nov 10 2022 06:53 AM
Solution
Flash Fill. In the gif, I use the menu command, but you could also do the shortcut ctrl+e.
Nov 10 2022 07:00 AM
ahh yes the flash fill. Awesome. Thank you very much.
Nov 10 2022 07:04 AM
Nov 10 2022 10:02 AM
HI Patrick, I am having a tough time making it work. My columns are next to eachother in the attached but I still cant get flash fill to work. Do you happen to see why?
In my real data set the columns are actually not next to eachother. Do you know of a work around? I could put the columns next to each other I guess since it would still be better than manually copying and pasting one by one.
thank you
Please see attached
Nov 10 2022 11:09 AM - edited Nov 10 2022 11:11 AM
Thank you for the followup and the sample workbook.
I was able to reproduce the same error you received. That particular error seems to be nowhere to be found online so I'll post it here in the hope it may come up when someone searches for it:
We didn't fill in values because the display formatting for some of the cells in the active column is using a different level of precision than the underlying value
The part Flash Fill doesn't like is the large decimal (7 numbers right of the decimal). Through some trial and error, I got it to work but only when I truncated the decimal down to 1. There's no issue with the cell formatting - it's all about the value in the formula bar. I've attached my sample workbook.
Nov 10 2022 07:16 PM
Nov 10 2022 07:44 PM
To copy-paste from visible cells to visible cells, try using “Sub CopyVisibleToVisible1” macro. You can put the macro in Personal.xlsb & assign the macro to a toolbar button, so you can use it on any open workbook.
You can find it here:
https://techcommunity.microsoft.com/t5/excel/paste-to-visible-cells-only-in-a-filtered-cells-only/m-...
Nov 11 2022 07:09 AM
Nov 11 2022 07:12 AM - edited Nov 11 2022 07:12 AM
From what I've read, Flash Fill uses "bits of code" to recognize patterns and fill out lists. However the feature is coded it must not be written for a decimal of that size.
Nov 11 2022 03:06 PM
Nov 10 2022 06:53 AM - edited Nov 10 2022 06:53 AM
Solution
Flash Fill. In the gif, I use the menu command, but you could also do the shortcut ctrl+e.