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.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

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.