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:

This article was written by Jeff Lenning

7 comments:

  1. Brian
    Reply

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

    1. Jeff Lenning Post author
      Reply

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

  2. Peggy Carpenter
    Reply

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

    1. Jeff Lenning Post author
      Reply

      Me too πŸ™‚

  3. Michael
    Reply

    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

    1. Jeff Lenning Post author
      Reply

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

  4. Robert H
    Reply

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.