Mind Blown CalCPA Article

Imagine this scenario: There are 120 CSV files in a folder on your network. Each CSV export contains the transactions for a single month, and there are 10 years’ worth of files. Your mission, should you choose to accept it, is to combine all of the transactions in these 120 files into a single Excel worksheet. That’s a lot of copy/paste.

Often in Excel, there are multiple ways to accomplish any given task. Some ways are faster than others. Let’s call the copy/paste approach the slow way. The fast way: We can have Excel combine these for us in less than a minute. No formulas. No macros. Just click a few buttons.

The detailed steps to accomplish this are covered in this CalCPA article:

Mind. Blown.

I’ve also put together a short video that demonstrates the steps in Excel:

Hope this helps you get your work done faster 🙂

Files

If you’d like to give it a try, feel free to download the zip file that contains the 120 CSV files:

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

9 Comments

  1. Brian on June 2, 2017 at 9:30 am

    Very nice! I had previously built my own macro to do this function, but this is even better! Great tool! Thanks for sharing.

    • Jeff Lenning on June 2, 2017 at 9:32 am

      I agree…These tools are able to replace lots of these kinds of macros…love that!

  2. Peggy Carpenter on June 4, 2017 at 3:07 pm

    You couldn’t wipe the smile off my face after I tried this the first time. So much power, so easy, so fast.

    • Jeff Lenning on June 4, 2017 at 3:27 pm

      Me too 🙂

  3. Michael on June 6, 2017 at 12:25 am

    Hi Jeff,
    I am engaged in this very kind of exercise and was amazed how easy it was. In my case however, I needed to retain the originating file name as some information was stored as part of the file name. Took a bit to find out how this is done, but like many things, once learned, it becomes second nature.

    Happy to send the instructions I typed up for my colleagues if you want to post them on your blog.
    Thanks,
    Michael

    • Jeff Lenning on June 6, 2017 at 2:35 am

      Michael, yes that would be great, please send then…Thank you!

  4. Robert H on July 2, 2017 at 10:49 am

    That’s a neat trick I didn’t know!

    I confess I wouldn’t even have looked for the solution within Excel. I’m of the old school, where file management is the job of the OS, not the application. I would have dropped to the command line in that folder and typed COPY *.CSV COMBINED.CSV and hit enter. It would have been just as fast, but I realize fewer and fewer people use the command line these days.

  5. Duncan Williamson on August 23, 2017 at 6:45 pm

    Thanks for all of these examples on Get & Transform, Jeff. Illuminating and very useful for me.

    I thought you might like to know that I tried to watch the video on this page on my iPad … wouldn’t work. It works without hesitation on my Windows laptop. I haven’t tried any of your other videos on my iPad so this is just a heads up, in case.

    Best wishes

    Duncan

    • Jeff Lenning on August 24, 2017 at 6:23 pm

      Thanks Duncan, I will check it out.

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.