• 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.

Copy with Filtered Data

Luke M

Excel Ninja
If you've ever tried to copy data from one column in a filtered list, to another column, you've discovered that Excel didn't keep all your data on the same row. See below
upload_2014-6-13_13-40-30.png
Ugh, it put all the data together, with the 37 value going into one of the rows that's currently hidden. That's no good! :(

However, there is a workaround. Select the two columns (if columns are not adjacent, you can hold Ctrl down to select separate ranges). Select only the visible cells (Ctrl+g, Special, Visible only).

Now you're ready for the magic! :cool: Check out these commands on Home ribbon, hiding on the right side
upload_2014-6-13_13-42-42.png

Instead of trying to do copy and paste, we're going to "fill left" from the Debit column to Credit column. Voila!
upload_2014-6-13_13-44-0.png

You could, of course, use the Fill left or Fill right, depending on which way you need the data to go. Anyway, that's my new trick that I learned this week, hope it helps someone else on their way to being awesome w/ Excel. :awesome:
 
Last edited:

Luke M

Excel Ninja
Hi Narayan,
Yep, looks like the same technique. I must say though, I'm not surprise that I'm not the first to discover anything new in XL. Too many amazing MVPs out there. :DD
 

NARAYANK991

Excel Ninja
Hi Luke ,

I don't think it's because they are MVPs ; I think they are more into Excel , reading and getting to know about the latest news , interacting with a lot of others on Excel , much more than you and I.

It works both ways ; because you are an MVP , you spend / are likely to spend more time with Excel ; because you spend more time with Excel , you may become an MVP.

Narayan
 
Top