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:
I’ve also put together a short video that demonstrates the steps in Excel:
Hope this helps you get your work done faster 🙂
If you’d like to give it a try, feel free to download the zip file that contains the 120 CSV files:
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
Very nice! I had previously built my own macro to do this function, but this is even better! Great tool! Thanks for sharing.
I agree…These tools are able to replace lots of these kinds of macros…love that!
You couldn’t wipe the smile off my face after I tried this the first time. So much power, so easy, so fast.
Me too 🙂
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.
Michael, yes that would be great, please send then…Thank you!
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.
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.
Thanks Duncan, I will check it out.